Re: How to parse XML in Postgres newer versions also

ne 17. 3. 2019 v 14:49 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

> Hi
>
> ne 17. 3. 2019 v 12:11 odesílatel Andrus <kobruleht2(at)hot(dot)ee> napsal:
>
>> Hi!
>>
>> In Postgres 9.1.2 script below produces proper results:
>>
>> 1.34
>> 5.56
>>
>> In Postgres 11 it produces wrong results:
>>
>> null
>> null
>>
>> How to make it also to work in newer versions on Postgres ?
>>
>> create temp table t(x xml, nsa text[][]) on commit drop;
>> insert into t values(
>> '<?xml version="1.0" encoding="UTF-8"?>
>> <Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>> xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
>> <BkToCstmrStmt>
>> <Stmt>
>> <Ntry>
>> <Amt Ccy="EUR">1.34</Amt>
>> </Ntry>
>> <Ntry>
>> <Amt Ccy="EUR">5.56</Amt>
>> </Ntry>
>> </Stmt>
>> </BkToCstmrStmt>
>> </Document> '::xml,
>> ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);
>>
>> SELECT
>> (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
>> FROM (
>> SELECT
>> unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
>> x,nsa)) as x,
>> nsa
>> FROM t
>> ) Ntry
>>
>> Andrus.
>>
>>
> This variant is working
> postgres=# SELECT
> (xpath('/ns:Ntry/ns:Amt/text()', x,nsa))[1]::text::numeric AS
> tasusumma
> FROM (
> SELECT
> unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
> x,nsa)) as x,
> nsa
> FROM t
> ) Ntry
> ;
>
> But I have not a idea, why old code doesn't work. It is little bit strange
> so it worked without namespace before Amt tag.
>
>
You can use XMLTABLE function

select xmltable.*
from t,
lateral
xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' as
ns),

'/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/.' passing t.x
columns tasusumma numeric path 'ns:Amt')

>
>
>