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

From Andrus
Subject Converting xml to table with optional elements
Date
Msg-id C8429D7D13224520A7DEB090C07A4A73@dell2
Whole thread Raw
Responses Re: Converting xml to table with optional elements  (David G Johnston <david.g.johnston@gmail.com>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Alexis
Date:
Subject: Re: How to avoid a GIN recheck condition
Next
From: David G Johnston
Date:
Subject: Re: Converting xml to table with optional elements