Re: Converting xml to table with optional elements - Mailing list pgsql-general

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