Обсуждение: xpath_table equivalent
Hello,<br /><br />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.<br /><br />Thanks,<br /><br /> ChrisGraner<br />
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@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/> 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