Postgres Pro
Downloads
Home   >   mailing lists

Re: Converting xml to table with optional elements - Mailing list pgsql-general

From David Johnston
Subject Re: Converting xml to table with optional elements
Date
Msg-id CAKFQuwZDHcb1P7Wj9UZ92MOkcUf4eqrTY-+dZ0WLaTjLCJ4TDQ@mail.gmail.com
Whole thread Raw
In response to Re: Converting xml to table with optional elements  ("Andrus" <kobruleht2@hot.ee>)
Responses Re: Converting xml to table with optional elements  ("Andrus" <kobruleht2@hot.ee>)
List pgsql-general
Tree view
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.

pgsql-general by date:

Previous
From: "Andrus"
Date:
Subject: Re: Converting xml to table with optional elements
Next
From: Adrian Klaver
Date:
Subject: Re: PG94RC1- plv8 functions - problem with input parameter length