Converting xml to table with optional elements

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Converting xml to table with optional elements
Date: 2014-11-27 13:54:19
Message-ID: C8429D7D13224520A7DEB090C07A4A73@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G Johnston 2014-11-27 15:03:29 Re: Converting xml to table with optional elements
Previous Message Alexis 2014-11-27 13:03:45 Re: How to avoid a GIN recheck condition