How to parse xml containing optional elements

["Andrus" <kobruleht2@xxxxxx>]

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. 



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general