Thread: How to parse XML in Postgres newer versions also

How to parse XML in Postgres newer versions also

From
"Andrus"
Date:
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. 



Re: How to parse XML in Postgres newer versions also

From
Pavel Stehule
Date:
Hi

ne 17. 3. 2019 v 12:11 odesílatel Andrus <kobruleht2@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.



 

Re: How to parse XML in Postgres newer versions also

From
Pavel Stehule
Date:


ne 17. 3. 2019 v 14:49 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
Hi

ne 17. 3. 2019 v 12:11 odesílatel Andrus <kobruleht2@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.


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')  




 

Re: How to parse XML in Postgres newer versions also

From
"Andrus"
Date:
Hi
 
>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.
In 9.1.5 it returns nulls
 
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?
Is it possible add some CASE WHEN or IF command  or any other idea ?
 
Andrus.

Re: How to parse XML in Postgres newer versions also

From
Thomas Kellerer
Date:
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;


Re: How to parse XML in Postgres newer versions also

From
"Andrus"
Date:
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.










Re: How to parse XML in Postgres newer versions also

From
Pavel Stehule
Date:


ne 17. 3. 2019 v 15:19 odesílatel Andrus <kobruleht2@hot.ee> napsal:
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

sure, XMLTABLE is supported from PostgreSQL 10.

Pavel


Andrus.








Re: How to parse XML in Postgres newer versions also

From
Pavel Stehule
Date:


ne 17. 3. 2019 v 15:11 odesílatel Andrus <kobruleht2@hot.ee> napsal:
Hi
 
>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.
In 9.1.5 it returns nulls
 
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?
Is it possible add some CASE WHEN or IF command  or any other idea ?

Probably this bugfix breaks your code


9.1 is unsupported release, so if you can, just fix it for supported releases.

else where, you need to prepare two different expressions.

Regards

Pavel
 
Andrus.

Re: How to parse XML in Postgres newer versions also

From
Tom Lane
Date:
"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