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

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:

tasusumma  orderinr
    150.00  PV04131
      0.38  PV04131

Since there is no EndToEnd in second row there should be null in second row
orderinr column. Correct result is:

tasusumma  orderinr
    150.00  PV04131
      0.38  null


How to fix this ?

    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>
          <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: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;

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.

Posted also in
http://stackoverflow.com/questions/38888739/how-to-parse-xml-with-optional-elements

Andrus.