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

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