Re: How to parse XML in Postgres newer versions also - Mailing list pgsql-general

"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