Re: How to parse XML in Postgres newer versions also

[Thomas Kellerer <spam_eater@xxxxxxx>]

Andrus schrieb am 17.03.2019 um 08:36:
> In Postgres 9.1.2 script below produces proper results:
>
> 1.34
> 5.56
>
> 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

If you don't need compatibility with old versions, I would use XMLTABLE in Postgres 11

    select x.*
    from t,
         xmltable(
              XMLNAMESPACES(t.nsa[1][2] AS ns),
              '/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry'
              passing t.x
              columns tasusumma numeric path 'ns:Amt'
         ) as x;