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