Re: [GENERAL] Converting xml to table with optional elements

[Andrus]

Hi!

You have to process this in two passes. First pass you create a table of 
documents by unnesting the non-optional >Document elements. Second pass you 
explode each individual row/document on that table into its components.

Thank you. I tried code below.  John Smith appears in result as "{"John 
Smith"}"
How to force it to appear as John Smith ?

Can this code improved, for example, merging create temp table ... select 
and update into single statement ?

Andrus.

   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 context="partner">
       <ContactData>
         <ActualAddress>
           <PostalCode>999999</PostalCode>
         </ActualAddress>
         <ContactFirstName>John Smith</ContactFirstName>
       </ContactData>
     </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);


create temp table temprid on commit drop as
   SELECT
           unnest(xpath('/E-Document/Document/DocumentInfo/DocumentNum/text()', 
x))::text AS docnumber,
           null::text as ContactFirstName,
           unnest(xpath('/E-Document/Document/DocumentItem/ItemEntry/SellerItemCode/text()', 
x))::text AS itemcode
           FROM t;

update temprid set ContactFirstName =xpath(
    
'/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x)::text
    from t ;

select * from temprid



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