Re: Converting xml to table with optional elements

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