Re: How to parse xml containing optional elements

Hi,

> Thank you.
> In "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by
> gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit"
> it returns two empty rows. How to make it work in this version ?

I couldn't really believe this so I just installed a VM and a 9.1
postgresql just to test this for you.

It seems you hit a bug in PostgreSQL prior to 9.1.15:
https://www.postgresql.org/docs/9.1/static/release-9-1-15.html

"Fix namespace handling in xpath() (Ali Akbar)

Previously, the xml value resulting from an xpath() call would not have
namespace declarations if the namespace declarations were attached to an
ancestor element in the input xml value, rather than to the specific
element being returned. Propagate the ancestral declaration so that the
result is correct when considered in isolation.
"

Given your current PG version, the queries will probably work if you
remove the "ns:" parts of the first two xpaths like this:

SELECT
(xpath('Amt/text()', x,nsa))[1]::text::numeric AS tasusumma,
(xpath('NtryDtls/TxDtls/Refs/EndToEndId/text()', x,nsa))[1] AS orderinr
FROM (
SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry',
x,nsa)) as x, nsa
FROM t
) Ntry

But that is not a good solution:
- when you eventually do upgrade, the query *will* break
- it depends on the exact location of the namespace declaration in the
source document. Your bank might change that in a way that will be still
perfectly valid, but break the assumptions made in that workaround.

So I suggest to upgrade to a supported version of the 9.1 branch from
your more than 5 years old build (which should be easy to do).

Best regards,

-hannes