Thread: Converting xml to table with optional elements

Converting xml to table with optional elements

From
"Andrus"
Date:
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



Re: Converting xml to table with optional elements

From
David G Johnston
Date:
Andrus Moor wrote
> 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.

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.

David J.



--
View this message in context:
http://postgresql.nabble.com/Converting-xml-to-table-with-optional-elements-tp5828506p5828515.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Converting xml to table with optional elements

From
"Andrus"
Date:
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



Re: Converting xml to table with optional elements

From
David Johnston
Date:
On Fri, Nov 28, 2014 at 4:17 AM, Andrus <kobruleht2@hot.ee> wrote:
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 ?

​Subquery the xpath expression to unnest it and apply a LIMIT 1

UPDATE tbl SET ... = (SELECT xpath( tbl.???[...] ) LIMIT 1)

This will cause either the first array element or NULL set to be the given column's value.

Note that I do not believe your example code is going to work.  As I mentioned you really want to create a table of documents and NOT try to pair up multiple unnested columns.

David J.

Re: Converting xml to table with optional elements

From
"Andrus"
Date:
Hi!
 
Thank you.
>Subquery the xpath expression to unnest it and apply a LIMIT 1
> UPDATE tbl SET ... = (SELECT xpath( tbl.???[...] ) LIMIT 1)
 
I used unnest() :
 
update temprid set
  ContactFirstName =unnest(xpath(
     '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text
 
Is this OK ?
 
 
>Note that I do not believe your example code is going to work.  As I mentioned you really want to create a >table of documents and NOT try to pair up multiple unnested columns.
 
How to create table of documents ?
 
xml contains multiple products and document info.
Code creates table containing one row for every product and adds same header fields to all rows.
Whu this will not work ?
 
Andrus.
 

Re: Converting xml to table with optional elements

From
David Johnston
Date:
On Fri, Nov 28, 2014 at 9:40 AM, Andrus <kobruleht2@hot.ee> wrote:
Hi!
 
Thank you.
>Subquery the xpath expression to unnest it and apply a LIMIT 1
> UPDATE tbl SET ... = (SELECT xpath( tbl.???[...] ) LIMIT 1)
 
I used unnest() :

​Sorry, I meant to say (SELECT unnest(xpath(tbl.???[...])) LIMIT 1)​...

 
update temprid set
  ContactFirstName =unnest(xpath(
     '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text
 
Is this OK ?

​It may be that the "SELECT" is optional - you should test it​.
 
 
 
>Note that I do not believe your example code is going to work.  As I mentioned you really want to create a >table of documents and NOT try to pair up multiple unnested columns.
 
How to create table of documents ?

​Instead of defining an xpath for fields define one that captures the xml pertaining to the data that would belong to a single record.​
 
 
xml contains multiple products and document info.
Code creates table containing one row for every product and adds same header fields to all rows.

​Sounds like you should have two tables...​
 
Whu this will not work ?
 

update temprid set
  ContactFirstName =unnest(xpath(
     '/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',x))::text

​I honestly don't know what the above does or will do in the presence of more than a single row on temprid.

Typically UPDATE table1 ... FROM table2 requires a WHERE clause of the form "WHERE table1.id = table2.id"...

David J.

Re: Converting xml to table with optional elements

From
"Andrus"
Date:
Hi!

Thank you.

>Instead of defining an xpath for fields define one that captures the xml
>pertaining to the data that would belong to
>a single record.How to create single xpath or xsl which assigns values to all columns in
>Postgres table ?
I havent found such sample. Samples which I have found create every column
separately using separate xpath.

Andrus



Re: Converting xml to table with optional elements

From
David Johnston
Date:

On Fri, Nov 28, 2014 at 10:47 AM, Andrus <kobruleht2@hot.ee> wrote:
Hi!

Thank you.

Instead of defining an xpath for fields define one that captures the xml pertaining to the data that would belong to
a single record.How to create single xpath or xsl which assigns values to all columns in Postgres table ?
I havent found such sample. Samples which I have found create every column separately using separate xpath.



I don't know - I'm not a heavy user of xpath/xml.  I do know that "text()" will not be of help because you have to return an entire node - xml elements included.  The textual representation of that node is then to be stored and all the extraction xpath expression executed against it.

David J.