How to parse XML in Postgres newer versions also

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&quot;
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.

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&quot;
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')

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.

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&quot;
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.

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

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

https://github.com/postgres/postgres/commit/79af9a1d2668c9edc8171f03c39e7fed571eeb98#diff-b0a58a99389873468848ebb302aa8185

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" <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