Re: Converting xml to table with optional elements

David Johnston <david.g.johnston@xxxxxxxxx> · Fri, 28 Nov 2014 09:55:34 -0700

On Fri, Nov 28, 2014 at 9:40 AM, Andrus <kobruleht2@xxxxxx> 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.