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