Search Postgresql Archives

Re: How to parse xml containing optional elements

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux