Wednesday, March 15, 2006

MySQL 5.1.7: XPath asymmetry between ExtractData() and UpdateXML() ?

The new MySQL 5.1.7 XML functions let you manipulate XML-formatted text strings using XPath query expressions.

You've got string ExtractValue(XML, XPath) - this let's you query a (sub)fragment of the XML fragment and returns the text content of the node that was found (if multiple nodes apply, the text contents of all these nodes are concatenated and separated by a space character, which is then returned). And, you've got XML UpdateXML(XML, XPath, XML) - this let's you search XML fragments and allows you to replace them with another XML fragment.

The funny thing is, it looks as though the XPath query is handled very differently in either case. Consider this little snippet (you might wan't to look at it on the original blog - some blog aggregates don't render the escaped XML fragments well):


mysql> select UpdateXML('<b>ccc<b>hhh</b></b>', '//b', '<e>fff</e>') c1
-> , ExtractValue('<b>ccc<b>hhh</b></b>','//b') c2
-> ;
+----------------------+---------+
| c1 | c2 |
+----------------------+---------+
| <b>ccc<b>hhh</b></b> | ccc hhh |
+----------------------+---------+
1 row in set (0.00 sec)

As you can see in the second column, the XPath correctly identifies all the <b> elements at all levels. The content of all the <b> elements is returned, literally.
However, the same XML, and the same XPath are used as an argument to UpdateXML()...NOTHING happens....I would've expected to see something like:


<b>fff<b>fff</b></b>

or

<b>fff</b>

Anyway, I filed a bug report.

No comments:

SAP HANA Trick: DISTINCT STRING_AGG

Nowadays, many SQL implementations offer some form of aggregate string concatenation function. Being an aggregate function, it has the effe...