Re: How to parse XML in Postgres newer versions also

[Pavel Stehule]

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