[GENERAL] Converting xml to table with optional elements

[Andrus]

How to convert xml to table if some elements are optional in xml ?
In XML

/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName
element is optional.

If this is present, code below works OK.
If ContactFirstName is not present , empty table is returned.

How to extract product code rows if ContactFirstName element is missing ?
In result ContactFirstName column should have null on other value.

Using Postgres 9.1

Andrus.

Testcase :

   create temp table t(x xml) on commit drop;
   insert into t values('<?xml version="1.0" encoding="UTF-8"?>
   <E-Document>
     <Document>
      <DocumentParties>
       <BuyerParty>
        <PartyCode>TEST</PartyCode>
        <Name>TEST</Name>
       </BuyerParty>
      </DocumentParties>
       <DocumentInfo>
         <DocumentNum>123</DocumentNum>
       </DocumentInfo>
       <DocumentItem>
         <ItemEntry>
           <SellerItemCode>9999999</SellerItemCode>
           <ItemReserve>
             <LotNum>(1)</LotNum>
             <ItemReserveUnit>
               <AmountActual>3.00</AmountActual>
             </ItemReserveUnit>
           </ItemReserve>
         </ItemEntry>
         <ItemEntry>
           <SellerItemCode>8888888</SellerItemCode>
           <ItemReserve>
             <LotNum>(2)</LotNum>
             <ItemReserveUnit>
               <AmountActual>3.00</AmountActual>
             </ItemReserveUnit>
           </ItemReserve>
         </ItemEntry>
       </DocumentItem>
     </Document>
   </E-Document>
   '::xml);

   SELECT
           unnest(xpath('/E-Document/Document/DocumentInfo/DocumentNum/text()',
x))::text AS docnumber,
           unnest( xpath(
    
'/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',
x))::text AS ContactFirstName,
           
unnest(xpath('/E-Document/Document/DocumentItem/ItemEntry/SellerItemCode/text()',
x))::text AS itemcode
           FROM t


Posted it also in

http://stackoverflow.com/questions/27171210/how-to-convert-xml-to-table-if-node-does-not-exist-in-postgres 



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