My So-Called Blog

I’ve written an article highlighting some recent developments with XML support in MySQL 5.1 and MySQL 6.0. Topics include:

• Outputting MySQL data in XML format; includes a look at the 3^rd-party lib_mysqludf_xql library
• Getting XML into MySQL; includes a discussion of the LOAD XML statement (new in MySQL 6.0)
• The ExtractValue() and UpdateXML() functions (new in MySQL 5.1)
• Security issues, including a look at a little nasty known as “XPath injection”

You can read it at the MySQL Developer Zone.

Alexander Barkov contributed a nifty stored procedure and a very helpful pre-publication review. Thanks, Bar!

Comments(0)

A few days ago, Alexander Barkov pushed some changes to the MySQL 5.1 tree that I’ve been waiting to see for some time — variable support for XPath functions used with ExtractValue() and UpdateXML(). (This was a fix for Bug #26518, BTW.) This will be available in MySQL 5.1.20 (or grab the MySQL 5.1 source from bkbits and build it yourself, if you just can’t wait).

Two slightly different notations are supported, depending on the context, and what sort of checking you want done on the values:

1. If you don’t want or need type checking, prefix the variable name with $@, like this: $@myvar. However, if you do this, and you make a typo, you’re on your own.
2. To make use of strict checking inside routines, leave off the @ sign, like this: $myvar.

This may not seem like a lot, but it greatly expands the usefulness of MySQL 5.1’s XML functions:

• You should now be able to import XML that uses practically any format into MySQL easily.
• This also makes it much easier to filter for specific rows/columns (or filter out unwated ones).
• You can easily do preprocessing with MySQL string functions like CONCAT(), REPLACE(), TRIM(), etc.

I’ve not yet had time to play with this and come up with some good examples for the Manual, but I’m really looking forward to doing so. Keep an eye out for them in the XML Functions section of the Manual.

Comments(1)