Andrus Andrus - 3 months ago 25
C# Question

How to parse xml with optional elements

SEPA ISO XML transactions file needs to be parsed into flat table in Postgres 9.1+ in ASP:NET 4.6 MVC controller.

I tried code below but this produces wrong result:

endaaa tasusumma orderinr
XX00221059842412 150.00 PV04131
XX00221059842412 0.38 PV04131


Since there is no EndToEnd in second row there should be null or empty text in second row orderinr column:

endaaa tasusumma orderinr
XX00221059842412 150.00 PV04131
XX00221059842412 0.38 null


How to fix this ?

Parsing can done in other ways e.q. using xslt stylesheet for tranformation or in client side ASP.NET 4.6 MVC if this is more reasonable.

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;


Update

Using code from Hannes answer returns empty rows in Postgres 9.1 How to make it work in 9.1 ?

Posted also in pgsql.general mailing list.

Answer

You need to extract all ns:Ntry elements first, and then get the amount and EndToEndId for each of them:

SELECT 
    endaaa,
    (xpath('ns:Amt/text()', x,nsa))[1]::text::numeric AS tasusumma,
    (xpath('ns:NtryDtls/ns:TxDtls/ns:Refs/ns:EndToEndId/text()', x,nsa))[1] AS orderinr

FROM (
    SELECT (xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Acct/ns:Id/ns:IBAN/text()', x,nsa))[1] as endaaa,
    unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry', x,nsa)) as x, nsa
    FROM t
) Ntry