Re: Converting xml to table with optional elements

On Fri, Nov 28, 2014 at 9:40 AM, Andrus <kobruleht2(at)hot(dot)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.