Re: xpath_table equivalent

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

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.

Scott Bailey

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

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

Scott Bailey wrote:
>
> I agree that the syntax of XMLTABLE is odd. But not demonstrably worse
> than xpathtable.

That's not saying much. I dislike both. Why the SQL committee feels the
need to invent arcane special case grammar rules is beyond me. I
understand why the author of xpathtable designed it the way he did, but
it's still ugly in my book.

As I said, with LATERAL we could produce a much cleaner functional
equivalent.

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

I think that's an orthogonal issue, really. There's probably a good case
for such a function whether or not we do something like xpath_table.

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

XQuery is a whole other question. Adding another library dependency is
something we try to avoid. Zorba <http://www.zorba-xquery.com/> might
work, but it appears to have its own impressive list of dependencies
(why does it require both libxml2 and xerces-c? That looks a bit redundant.)

Even if we did implement XMLTABLE, I think I'd probably be inclined to
start by limiting it to plain XPath, without the FLWOR stuff. I think
that would satisfy the vast majority of needs, although you might feel
differently. (Do a Google for XMLTABLE - every example I found uses
plain XPath expressions.)

cheers

andrew

On Thu, Nov 19, 2009 at 12:03 AM, Scott Bailey <artacus(at)comcast(dot)net> wrote:
> 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.

It's very much not compatible. Berkeley DB is not free for commercial
use. I anticipate that this would be a problem both for commericial
users of PostgreSQL and also for commercial PostgreSQL forks.
Besides, that's a lot of code to suck into Postgres to do, uh, a lot
of things that we already do in other ways.

...Robert

Robert Haas wrote:
> On Thu, Nov 19, 2009 at 12:03 AM, Scott Bailey <artacus(at)comcast(dot)net> wrote:
>
>> 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.
>>
>
> It's very much not compatible. Berkeley DB is not free for commercial
> use. I anticipate that this would be a problem both for commericial
> users of PostgreSQL and also for commercial PostgreSQL forks.
> Besides, that's a lot of code to suck into Postgres to do, uh, a lot
> of things that we already do in other ways.
>
>
>

XQuilla, however, is not Berkely DB. And its license is Apache v2. It is
built on Xerces-C, although it appears at first glance to have less
dependencies that Zorba. I'm not sure how pluggable the XML parser
engine is (or could be made).

cheers

andrew

I wrote:
>>
>> 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.
>>
>>
>
> XQuery is a whole other question. Adding another library dependency is
> something we try to avoid. Zorba <http://www.zorba-xquery.com/> might
> work, but it appears to have its own impressive list of dependencies
> (why does it require both libxml2 and xerces-c? That looks a bit
> redundant.)
>
> Even if we did implement XMLTABLE, I think I'd probably be inclined to
> start by limiting it to plain XPath, without the FLWOR stuff. I think
> that would satisfy the vast majority of needs, although you might feel
> differently. (Do a Google for XMLTABLE - every example I found uses
> plain XPath expressions.)
>
>

I did look at this a bit further. Sadly, XQilla's XSLT support is stated
to be of alpha quality, and missing some quite necessary features (e.g.
xsl:output). That pretty much rules out for now Xerces-C+XQilla as an
alternative xml stack to libxml2+libxslt, ISTM.

cheers

andrew