Thread: xpath not a good replacement for xpath_string
Sorry to bring this up, I know you've been fighting about XML for a while.
Currently, I am using XML2 functionality and have tried to get the newer
XPath function to work similarly, but can't quite seem to do it.
I think the current xpath function is too limited. (The docs said to post
problems to hackers if I have an issue.)
For instance, we have a web application that uses java with an XML class
serializer/deserializer Xstream. It creates XML that looks like this:
<com.company.local.myclass> <uuid>b5212259-a91f-4dca-a547-4fe89cf2f32c</uuid> <email>joe@somedomain.com</email>
</com.company.local.myclass>
My current strategy is to use xml2 as:
select xpath_string(datum, E'/com\.company\.local\.myclass/uuid) as uuid
from table;
Which produces a usable:
b5212259-a91f-4dca-a547-4fe89cf2f32c
I have been trying to use xpath
select xpath(E'/com\.company\.local\.myclass/uuid', XMLPARSE(CONTENT
datum)) as uuid from table;
Which produces an unusable:
{<uuid>b5212259-a91f-4dca-a547-4fe89cf2f32c</uuid>}
On Tue, Jul 28, 2009 at 3:21 PM, <pgsql@mohawksoft.com> wrote:
> Sorry to bring this up, I know you've been fighting about XML for a while.
>
> Currently, I am using XML2 functionality and have tried to get the newer
> XPath function to work similarly, but can't quite seem to do it.
>
> I think the current xpath function is too limited. (The docs said to post
> problems to hackers if I have an issue.)
>
> For instance, we have a web application that uses java with an XML class
> serializer/deserializer Xstream. It creates XML that looks like this:
>
> <com.company.local.myclass>
> <uuid>b5212259-a91f-4dca-a547-4fe89cf2f32c</uuid>
> <email>joe@somedomain.com</email>
> </com.company.local.myclass>
>
> My current strategy is to use xml2 as:
>
> select xpath_string(datum, E'/com\.company\.local\.myclass/uuid) as uuid
> from table;
>
> Which produces a usable:
> b5212259-a91f-4dca-a547-4fe89cf2f32c
>
> I have been trying to use xpath
> select xpath(E'/com\.company\.local\.myclass/uuid', XMLPARSE(CONTENT
> datum)) as uuid from table;
>
> Which produces an unusable:
> {<uuid>b5212259-a91f-4dca-a547-4fe89cf2f32c</uuid>}
>
How about:
SELECT (xpath(E'/com\.company\.local\.myclass/uuid/text()',
XMLPARSE(CONTENT datum)))[1] as uuid from table;
Not as clean, but it produces the same result as xpath_string().
Combined with array_to_string() could can collapse the array instead
of just grabbing the first element (in cases other than uuid, of
course).
--
Mike Rylander| VP, Research and Design| Equinox Software, Inc. / The Evergreen Experts| phone: 1-877-OPEN-ILS
(673-6457)|email: miker@esilibrary.com| web: http://www.esilibrary.com
pgsql@mohawksoft.com wrote:
> Sorry to bring this up, I know you've been fighting about XML for a while.
>
> Currently, I am using XML2 functionality and have tried to get the newer
> XPath function to work similarly, but can't quite seem to do it.
>
> I think the current xpath function is too limited. (The docs said to post
> problems to hackers if I have an issue.)
>
> For instance, we have a web application that uses java with an XML class
> serializer/deserializer Xstream. It creates XML that looks like this:
>
> <com.company.local.myclass>
> <uuid>b5212259-a91f-4dca-a547-4fe89cf2f32c</uuid>
> <email>joe@somedomain.com</email>
> </com.company.local.myclass>
>
> My current strategy is to use xml2 as:
>
> select xpath_string(datum, E'/com\.company\.local\.myclass/uuid) as uuid
> from table;
>
> Which produces a usable:
> b5212259-a91f-4dca-a547-4fe89cf2f32c
>
> I have been trying to use xpath
> select xpath(E'/com\.company\.local\.myclass/uuid', XMLPARSE(CONTENT
> datum)) as uuid from table;
>
> Which produces an unusable:
> {<uuid>b5212259-a91f-4dca-a547-4fe89cf2f32c</uuid>}
>
>
>
>
This is really a usage question, which doesn't belong on -hackers.
However, here is how to do what you want:
andrew=# select
(xpath($$/com.company.local.myclass/uuid/text()$$,$$<com.company.local.myclass>
<uuid>b5212259-a91f-4dca-a547-4fe89cf2f32c</uuid> <email>joe@somedomain.com</email>
</com.company.local.myclass>
$$::xml))[1]::text as uuid; uuid
--------------------------------------b5212259-a91f-4dca-a547-4fe89cf2f32c
(1 row)
cheers
andrew
Andrew Dunstan <andrew@dunslane.net> wrote: > This is really a usage question, which doesn't belong on -hackers. Perhaps this sentence in the 8.4.0 docs should be amended or removed?: "If you find that some of the functionality of this module is not available in an adequate form with the newer API, please explain your issue to pgsql-hackers@postgresql.org so that the deficiency can be addressed." http://www.postgresql.org/docs/8.4/interactive/xml2.html#AEN115231 -Kevin
Kevin Grittner wrote: > Andrew Dunstan <andrew@dunslane.net> wrote: > > >> This is really a usage question, which doesn't belong on -hackers. >> > > Perhaps this sentence in the 8.4.0 docs should be amended or removed?: > > "If you find that some of the functionality of this module is not > available in an adequate form with the newer API, please explain your > issue to pgsql-hackers@postgresql.org so that the deficiency can be > addressed." > > http://www.postgresql.org/docs/8.4/interactive/xml2.html#AEN115231 > > > Well, yes, maybe I should withdraw my comment, although in fact the desired functionality is present, as both Mike Rylander and I noted. You just need to use the text() function to get the contents of the node, and an array subscript to pull it out of the result array. The really annoying thing we are missing is not xpath functionality, but XSLT processing. cheers andrew
Andrew Dunstan <andrew@dunslane.net> wrote: > in fact the desired functionality is present [...] You just need to > use the text() function to get the contents of the node, and an > array subscript to pull it out of the result array. I just took a quick look, and that didn't jump out at me from the documentation. Perhaps there should be an example or two of how to get the equivalent functionality through the newer standard API, for those looking to migrate? Would it make sense to supply convenience SQL functions which map some of the old API to the new? -Kevin
> Andrew Dunstan <andrew@dunslane.net> wrote: > >> in fact the desired functionality is present [...] You just need to >> use the text() function to get the contents of the node, and an >> array subscript to pull it out of the result array. > > I just took a quick look, and that didn't jump out at me from the > documentation. Perhaps there should be an example or two of how to > get the equivalent functionality through the newer standard API, for > those looking to migrate? > > Would it make sense to supply convenience SQL functions which map > some of the old API to the new? The thing that perplexed me was that it was not obvious from the docs how, exactly, to get the functionality that was simple and straight forward in XML2. Another thing that is troubling is that more exotic types do not seem to be supported at all. For instance, in my example I used uuid, and if one substitutes "uuid()" for "text()" that doesn't work. The API is less intuitive than the previous incarnation and is, indeed, more difficult to use. > > -Kevin > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Tuesday, July 28, 2009, <pgsql@mohawksoft.com> wrote: >> Andrew Dunstan <andrew@dunslane.net> wrote: >> >>> in fact the desired functionality is present [...] You just need to >>> use the text() function to get the contents of the node, and an >>> array subscript to pull it out of the result array. >> >> I just took a quick look, and that didn't jump out at me from the >> documentation. Perhaps there should be an example or two of how to >> get the equivalent functionality through the newer standard API, for >> those looking to migrate? >> >> Would it make sense to supply convenience SQL functions which map >> some of the old API to the new? > > The thing that perplexed me was that it was not obvious from the docs how, > exactly, to get the functionality that was simple and straight forward in > XML2. > > Another thing that is troubling is that more exotic types do not seem to > be supported at all. For instance, in my example I used uuid, and if one > substitutes "uuid()" for "text()" that doesn't work. > text() is an XPath function used to extract the text content of a node, in this case the uuid element. > The API is less intuitive than the previous incarnation and is, indeed, > more difficult to use. It may be easier to use for those not familiar with more advanced XPath, but it also has non-standard default actions. That being said, I'd love to see wrapper functions that provide the older api but leverage the core code. --miker -- Mike Rylander| VP, Research and Design| Equinox Software, Inc. / The Evergreen Experts| phone: 1-877-OPEN-ILS (673-6457)|email: miker@esilibrary.com| web: http://www.esilibrary.com
pgsql@mohawksoft.com wrote: > > Another thing that is troubling is that more exotic types do not seem to > be supported at all. For instance, in my example I used uuid, and if one > substitutes "uuid()" for "text()" that doesn't work. > > text() is an XPath function, with well defined semantics that have no relation at all to types in a PostgreSQL sense. uuid() of course has no meaning in an XPath expression. cheers andrew
On Tuesday 28 July 2009 23:30:23 pgsql@mohawksoft.com wrote: > The thing that perplexed me was that it was not obvious from the docs how, > exactly, to get the functionality that was simple and straight forward in > XML2. I continue to be in favor of adding xpath_string xpath_number xpath_boolean functions, which would be both easier to use and provide a more casting-free approach to pass the data around. In the past there were some doubts and objections about that, but I think it could be done.
> On Tuesday 28 July 2009 23:30:23 pgsql@mohawksoft.com wrote: >> The thing that perplexed me was that it was not obvious from the docs >> how, >> exactly, to get the functionality that was simple and straight forward >> in >> XML2. > > I continue to be in favor of adding > > xpath_string > xpath_number > xpath_boolean > > functions, which would be both easier to use and provide a more > casting-free > approach to pass the data around. In the past there were some doubts and > objections about that, but I think it could be done. > I totally agree, but I tend to be more of a pragmatist than a purist. It seems to me that purists tend to like a lot of topical consistency in an API, like the new implementation of xpath over the former. Where as a pragmatists will violate some of the rules to make something seemingly more easy. The issue I have with the xpath implementation is that it seems more geared to an XML implementation on top of SQL instead of an XML implementation embedded within SQL. For instance, I use an XML column in a database for "metadata" about customers and other objects. So, we have a base table of "objects" and the specifics of each object is contained within XML. So, the former API was perfect for this use: select datum form objects were key ='GUID' and xpath_string(datum,E'foo/bar') = 'frobozz'; The logic of the function seems is that it is intended to use extracted XML within a query. The new xpath functionality seems not to be designed to facilitate this, requiring a pretty arcane query structure to do the same thing: select datum from objects where key='GUID' and (xpath(E'foo/bar', XMLPARSE(CONTENT datum))::text())[1] = 'frobozz';
pgsql@mohawksoft.com wrote: >> On Tuesday 28 July 2009 23:30:23 pgsql@mohawksoft.com wrote: >> >>> The thing that perplexed me was that it was not obvious from the docs >>> how, >>> exactly, to get the functionality that was simple and straight forward >>> in >>> XML2. >>> >> I continue to be in favor of adding >> >> xpath_string >> xpath_number >> xpath_boolean >> >> functions, which would be both easier to use and provide a more >> casting-free >> approach to pass the data around. In the past there were some doubts and >> objections about that, but I think it could be done. >> >> > > I totally agree, but I tend to be more of a pragmatist than a purist. It > seems to me that purists tend to like a lot of topical consistency in an > API, like the new implementation of xpath over the former. Where as a > pragmatists will violate some of the rules to make something seemingly > more easy. > > The issue I have with the xpath implementation is that it seems more > geared to an XML implementation on top of SQL instead of an XML > implementation embedded within SQL. > > For instance, I use an XML column in a database for "metadata" about > customers and other objects. So, we have a base table of "objects" and the > specifics of each object is contained within XML. > > > So, the former API was perfect for this use: > > select datum form objects were key ='GUID' and > xpath_string(datum,E'foo/bar') = 'frobozz'; > > The logic of the function seems is that it is intended to use extracted > XML within a query. The new xpath functionality seems not to be designed > to facilitate this, requiring a pretty arcane query structure to do the > same thing: > > select datum from objects where key='GUID' and (xpath(E'foo/bar', > XMLPARSE(CONTENT datum))::text())[1] = 'frobozz'; > > It's not that arcane. Mike Rylander and I came up with the same answer independently within a very short time of you posting your query. I guess it depends how used you are to using XPath. It's also probably not terribly hard to produce a wrapper to do what you'd like. I have no problem with adding some convenience functions. I do have a problem with functions where we try to make things easy and instead muck them up. We just ripped out a "convenience" from our xpath processing that was totally braindead, so this isn't an idle concern. I would argue that "xpath_string" is a fairly horrible name for what the xml2 module provides. Specifically, my objection is that an xpath query returns a nodeset, and what this function returns is not the string value of the nodeset, but the string value of the *contents* of those nodes, which is not the same thing at all. To that extent the xml2 module documentation is at best imprecise and at worst plain wrong. cheers andrew
>> >> select datum form objects were key ='GUID' and >> xpath_string(datum,E'foo/bar') = 'frobozz'; >> >> The logic of the function seems is that it is intended to use extracted >> XML within a query. The new xpath functionality seems not to be designed >> to facilitate this, requiring a pretty arcane query structure to do the >> same thing: >> >> select datum from objects where key='GUID' and (xpath(E'foo/bar', >> XMLPARSE(CONTENT datum))::text())[1] = 'frobozz'; >> >> > > It's not that arcane. Mike Rylander and I came up with the same answer > independently within a very short time of you posting your query. I > guess it depends how used you are to using XPath. That is sort of the point I was making. It just seems arcane, by the very definition of arcane, within a SQL context. It is workable and it can be used, but I don't think the change was designed to make writing queries easier. It was designed to be more about "XPath" than SQL. > > It's also probably not terribly hard to produce a wrapper to do what > you'd like. No, it isn't but you haven't made the usage of XPath any easier in the more general case. > > I have no problem with adding some convenience functions. I do have a > problem with functions where we try to make things easy and instead muck > them up. We just ripped out a "convenience" from our xpath processing > that was totally braindead, so this isn't an idle concern. > > I would argue that "xpath_string" is a fairly horrible name for what the > xml2 module provides. Specifically, my objection is that an xpath query > returns a nodeset, and what this function returns is not the string > value of the nodeset, but the string value of the *contents* of those > nodes, which is not the same thing at all. To that extent the xml2 > module documentation is at best imprecise and at worst plain wrong. Well, the API is there, it is where, I guess, PostgreSQL is going, but I think, philosophically, the API needs to see the XML contained within SQL columns as being able to represent variable and optional columns in object oriented environments easily. The harder it is to use a feature, the less usable the feature is. Do you disagree? > > cheers > > andrew > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
pgsql@mohawksoft.com wrote: > > Well, the API is there, it is where, I guess, PostgreSQL is going, but I > think, philosophically, the API needs to see the XML contained within SQL > columns as being able to represent variable and optional columns in object > oriented environments easily. The harder it is to use a feature, the less > usable the feature is. > > Do you disagree? > > There is always a degree of tradeoff between power and ease of use. But whether or not you like the way the xpath() function now works hardly matters - we're not going to change the behaviour of an existing function except to fix a bug. As I said upthread, I think we can easily provide some extra convenience functions which will do what you want. The only thing I was really arguing about was the function name for such a gadget. cheers andrew
On Jul 29, 2009, at 12:12 PM, Andrew Dunstan wrote: > As I said upthread, I think we can easily provide some extra > convenience functions which will do what you want. The only thing I > was really arguing about was the function name for such a gadget. +1. "xpath_string" does seem unfortunate, but I'm not offended by it. =)