Thread: Converting xml to table with optional elements

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

Andrus Moor wrote
> 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.

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.

David J.



--
View this message in context:
http://postgresql.nabble.com/Converting-xml-to-table-with-optional-elements-tp5828506p5828515.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Hi!

Thank you.

>Instead of defining an xpath for fields define one that captures the xml
>pertaining to the data that would belong to
>a single record.How to create single xpath or xsl which assigns values to all columns in
>Postgres table ?
I havent found such sample. Samples which I have found create every column
separately using separate xpath.

Andrus