Re: How to parse xml containing optional elements

Hi!

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

Psotgres 9.1 run isn Debian Squeeze which is unsupported.
How to upgrade in Debian Squeeze ?

How to add IBAN column to result table? This column has same value for all
rows.

create temp table t(x xml, nsa text[][]) on commit drop;
insert into t values(
'<?xml version=''1.0'' encoding=''UTF-8''?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
<BkToCstmrStmt>
<Stmt>
<Acct>
<Id>
<IBAN>XX00221059842412</IBAN>
</Id>
</Acct>
<Ntry>
<Amt Ccy="EUR">150.00</Amt>
<NtryDtls>
<TxDtls>
<Refs>
<EndToEndId>PV04131</EndToEndId>
</Refs>
</TxDtls>
</NtryDtls>
</Ntry>
<Ntry>
<Amt Ccy="EUR">0.38</Amt>
<NtryDtls>
<TxDtls>
<Refs>
<AcctSvcrRef>2016080100178214-2</AcctSvcrRef>
</Refs>
</TxDtls>
</NtryDtls>
</Ntry>
</Stmt>
</BkToCstmrStmt>
</Document>
', ARRAY[ARRAY['ns','urn:iso:std:iso:20022:tech:xsd:camt.053.001.02']]);

SELECT
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Acct/ns:Id/ns:IBAN/text()',
x,nsa))::text AS endaaa,
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/ns:Amt/text()',
x,nsa))::text::numeric AS tasusumma
,
unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/ns:NtryDtls/ns:TxDtls/ns:Refs/ns:EndToEndId/text()',
x,nsa))::text AS orderinr
FROM t;

should produce

endaaa tasusumma orderinr
XX00221059842412 150.00 PV04131
XX00221059842412 0.38 null

Andrus.