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
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 |