Re: xpath_table equivalent

Scott Bailey wrote:
> Chris Graner wrote:
>> Hello,
>>
>> I've been reading over the documentation to find an alternative to
>> the deprecated xpath_table functionality. I think it may be a
>> possibility but I'm not seeing a clear alternative.
>>
>> Thanks,
>>
>> Chris Graner
>
> The standard is XMLTABLE and is implemented by both db2 and oracle but
> is on our list of unimplemented features. I would love to see this
> implemented in Postgres. I recall it coming up here before. But I
> don't think it went beyond discussing which xquery library we could use.
>
>

Yes, Chris spoke to me about this last night and emailed me an example
of what he needs today, and I've spent the couple of hours thinking
about it. Not have a nice way of getting a recordset out of a piece of
XML is actually quite a gap in our API.

The trouble is that XMLTABLE is a horrible grammatical mess, ISTM, and I
don't much like the way xpath_table() works either. Passing a table name
as text into a function is rather ugly.

I think we could do with a much simple, albeit non-standard, API.
Something like:

xpathtable(source xml, rootnodes text, leaves variadic text[])
returns setof record

But unless I'm mistaken we'd need the proposed LATERAL extension to make
it iterate nicely over a table. Then we could possibly do something like:

select x.bar, x.blurfl
from
foo f,
lateral
xpathtable(f.xmlfield,'//foo','bar','baz[1]/blurfl','@is-some-property')
as x(bar int, blurfl text, xmlprop bool)
where f.otherfield or x.xmlprop;

cheers

andrew