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