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
