Thread: How to parse XML in Postgres newer versions also

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. 

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;

Hi!

>You can use XMLTABLE function
>select xmltable.*
>  from t,
>          lateral 
> xmltable(xmlnamespaces('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' as 
> ns),
> 
> '/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/.' passing t.x
>                                 columns tasusumma numeric path 'ns:Amt')

In 9.1.5 this throws error

ERROR: syntax error at or near "("
LINE 20: lateral xmltable(xmlnamespaces(
                                                                    ^
SQL state: 42601
Character: 582

Andrus.

"Andrus" <kobruleht2@hot.ee> writes:
>> 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. 

> In 9.1.5 without namespaces
> (xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma
> works.

> How to make it work in both versions?

I'm hardly an XML expert, but I think the behavior change that's getting
you came in with these commits:

    Author: Peter Eisentraut <peter_e@gmx.net>
    Branch: REL9_4_STABLE Release: REL9_4_1 [6bbf75192] 2015-01-17 22:11:20 -0500
    Branch: REL9_3_STABLE Release: REL9_3_6 [e32cb8d0e] 2015-01-17 22:13:27 -0500
    Branch: REL9_2_STABLE Release: REL9_2_10 [c8ef5b1ac] 2015-01-17 22:14:21 -0500
    Branch: REL9_1_STABLE Release: REL9_1_15 [c975fa471] 2015-01-17 22:37:07 -0500
    Branch: REL9_0_STABLE Release: REL9_0_19 [cebb3f032] 2015-01-17 22:37:32 -0500

        Fix namespace handling in xpath function

        Previously, the xml value resulting from an xpath query would not have
        namespace declarations if the namespace declarations were attached to
        an ancestor element in the input xml value.  That means the output value
        was not correct XML.  Fix that by running the result value through
        xmlCopyNode(), which produces the correct namespace declarations.

        Author: Ali Akbar <the.apaan@gmail.com>


As things currently work, the lower xpath call is producing
namespace-labeled XML:

regression=# SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x
        FROM t;
                               x
---------------------------------------------------------------
 <Ntry xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">+
         <Amt Ccy="EUR">1.34</Amt>                            +
       </Ntry>
 <Ntry xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">+
         <Amt Ccy="EUR">5.56</Amt>                            +
       </Ntry>
(2 rows)

so you have to attach namespaces to the elements of the upper xpath
call if you want a match:

regression=# SELECT                                                                 (xpath('ns:Ntry/ns:Amt/text()',
x,nsa))AS tasusumma 
    FROM (
        SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x,
        nsa
        FROM t
    ) Ntry
;
 tasusumma
-----------
 {1.34}
 {5.56}
(2 rows)


but of course that'd fail in 9.1.5 because the output of the lower
xpath call bears no namespace labels :-(.  (Or so I think anyway,
I don't have any such installation handy to try.)

The behavior you're seeing in 9.1.5 is just broken, so ideally what
you'd do to resolve the cross-version discrepancy is update that
installation to 9.1.15 or later.  Or perhaps you could drop the usage
of namespaces from this query?  I think the old and new behaviors are
the same if no namespaces are involved.

            regards, tom lane