Re: How to parse XML in Postgres newer versions also

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

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