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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
