Re: How to parse XML in Postgres newer versions also

[Pavel Stehule]

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.