Re: How to parse XML in Postgres newer versions also

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;