xpath_table equivalent

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/&gt; 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@comcast.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@comcast.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/&gt; 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