Re: Converting xml to table with optional elements

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