Re: How to parse XML in Postgres newer versions also

[Pavel Stehule]

ne 17. 3. 2019 v 14:49 odesílatel Pavel Stehule <pavel.steh...@gmail.com>
napsal:

> Hi
>
> ne 17. 3. 2019 v 12:11 odesílatel Andrus <kobrule...@hot.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')



>
>
>