Re: xpath_table equivalent

Andrew Dunstan wrote:
>

>>> 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

I agree that the syntax of XMLTABLE is odd. But not demonstrably worse
than xpathtable. If we are going to exert effort on it, why not do it in
a standards compliant way? Otherwise I'd suggest a stop gap of just
adding some support functions to make it easier to extract a scalar
value from a node. Something like what I did here.

http://scottrbailey.wordpress.com/2009/06/19/xml-parsing-postgres/

The nice thing about XMLTABLE is that it adds xquery support. I think
the majority of xquery engines seem to be written in Java. XQuilla is
C++. I'm not sure if our licensing is compatible, but it I would love
the irony of using Berkeley DB XML (formerly Sleepycat) now that its
owned by Oracle.

Scott