Re: 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(at)somedomain(dot)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(at)mohawksoft(dot)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(at)somedomain(dot)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(at)esilibrary(dot)com
| web: http://www.esilibrary.com

pgsql(at)mohawksoft(dot)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(at)somedomain(dot)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(at)somedomain(dot)com</email>
</com.company.local.myclass>
$$::xml))[1]::text as uuid;
uuid
--------------------------------------
b5212259-a91f-4dca-a547-4fe89cf2f32c
(1 row)

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)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(at)postgresql(dot)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(at)dunslane(dot)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(at)postgresql(dot)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(at)dunslane(dot)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(at)dunslane(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Tuesday, July 28, 2009, <pgsql(at)mohawksoft(dot)com> wrote:
>> Andrew Dunstan <andrew(at)dunslane(dot)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(at)esilibrary(dot)com
| web: http://www.esilibrary.com

pgsql(at)mohawksoft(dot)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(at)mohawksoft(dot)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(at)mohawksoft(dot)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(at)mohawksoft(dot)com wrote:
>> On Tuesday 28 July 2009 23:30:23 pgsql(at)mohawksoft(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

pgsql(at)mohawksoft(dot)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. =)