Re: How to parse xml containing optional elements - Mailing list pgsql-general

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.