Re: Converting xml to table with optional elements
От | Andrus |
---|---|
Тема | Re: Converting xml to table with optional elements |
Дата | |
Msg-id | 22925FBD3A174BEE8D22AAEFFD97BA5D@dell2 обсуждение исходный текст |
Ответ на | Converting xml to table with optional elements ("Andrus" <kobruleht2@hot.ee>) |
Ответы |
Re: Converting xml to table with optional elements
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: