Re: How to parse XML in Postgres newer versions also

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.