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;
