MySQL 5.1 XML Support

A better title would be, "not really."

Once again, MySQL is way behind the rest of the database world in functionality. Reading through the manual for what is as of this posting the current version, 5.1, the creators proudly advertise XML support. Unfortunately, the implementation is sufficiently incomplete as to be mostly useless.

Here's the problem: good XML is namespaced. Unless you're creating one off XML for extremely short term use, your dataset should contain and be constrained by some sort of definition. I personally prefer XML Schema, but there are other options such as DTD or Relax-NG. I think XML Schema is the de facto standard because it's most used and supported in most implementations, but I've heard from people who use Relax-NG frequently that it's a better standard.

None of the MySQL 5.1 XML functions support namespacing. They claim to, but they don't. They support namespace prefixing, but a prefix is useless without a namespace URI behind it. Prefixing an element does not namespace it; prefixing an element is a shortcut to make it easier and less verbose to longhand namespace it. But the prefix must be tied to a namespace, in the current context. If you are querying an XML document with XPath, the current context is the query, not the original document. Therefore, the prefix must be tied to a namespace within the query itself. Any compliant XPath querying function must take three arguments (somehow): the XML listing to query, the query and a map of prefixes to namespace URIs. Without the third argument, the prefix-to-URI map, it is impossible to correctly query a namespace-qualified document, which, if you're doing XML correctly, is pretty much every document.

Re-using or borrowing prefix-to-URI mappings from the source document is invalid. The mapping in the document is applicable on a document basis, not on a schema basis. That is, the mapping can change across instances of the document and still be valid within the schema. Given that, if you wanted to query a document and only know the schema, which is almost always the case at design time, you'd have no way to know what prefix is mapped to what URI. Therefore, you'd have no way of constructing a valid query containing prefixes.

Unfortunately, the manual page does not namespace-qualify any nodes in their examples, which is fairly common in XML examples. The only documentation mentioning namespacing refers to the ability to use prefixes and points the reader to a defunct Apache project. I hope that this was an oversight on the part of the MySQL developer team, probably an indication that whoever wrote the XML support has never used XML in a real-world context.

For what it's worth, other RDBMS platforms do support real XML querying. Oracle 10g's EXTRACTVALUE does support a half-baked but functional namespace URI specification in calling. Microsoft SQL Server has supported a fully namespace-qualified query on the XML data type with the query T-SQL keyword since their 2005 version. Even Postgres -- "even" only because it's open source and a direct competitor to MySQL -- has a fully compliant xpath function that takes arbitrary prefix-to-URI mappings.