XML Moves to mySQL

by Kurt Cagle

The unification of XML and SQL relational data has taken another significant step forward recently with the introduction of significant new XML functionality in mySQL, the world's most popular open source database. In versions 5.1 and 6.0, mySQL adds the ability to retrieve tables (and JOINS) as XML results, to retrieve SQL schemas as XML files, to both select content via a subset of XPath and to update content using similar functions, and the like, as related recently in an article on the mySQL site: http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html .


2007-12-24 05:13:56
Great news for the "full XML stack" junkies: RIAs favour XML technologies throughout all Web Application Tiers..?
2007-12-29 06:21:01
I'm sorry. I expected to see support for SQL/XML, XQuery, or some other interoperable standard. I prefer MySQL to any other relational database, but this looks like some proprietary hacks that come up very short of what I would consider native XML support. XQuery 1.0 contains XPath 2.0 as a subset - what's with "a subset of XPath functionality"?

I'll continue using MySQL for relational work, and separate native XML databases for XML storage. (Actually, I find eXist's SQL plugin very handy for manipulating MySQL results in XML.)

Kurt Cagle
2007-12-29 18:06:40

I've found sometimes that it is worth encouraging companies and organizations that have taken their first baby steps in the right direction to continue to do so, rather than complaining that these steps were not sufficient. I would prefer to see a SQL-XML or XQuery (preferably the latter layer) in place as well, and like you I typically tend to use mySQL via eXist in order to provide this support, but especially with an OSS project, you take what you can get.

2007-12-30 05:40:57
You're right. After I posted that, I regretted it. It did sound too negative. I should have gone about it another way, such as:

* ISO/IEC 9075-14:2006 (SQL/XML Specification)
* XQuery 1.0: An XML Query Language
* XML Path Language (XPath) 2.0
* XQuery Update Facility 1.0

The concept of a hybrid relational/XML database really excites me. I've dabbled with DB2's SQL/XML support with mixed results. Full text search using XPath was fast and efficient in Tamino, complex and full of proprietary add ons with DB2. I'd really like to avoid that in the future. And I'd really like to see the MySQL team come through with a standard implementation that doesn't require such proprietary extensions.

The MySQL tech resources article mentioned above appears to be laying a very low-level API onto which the above mentioned standards can be built. Implementing the full XPath 2.0 spec will take time (DB2 didn't in the first rendition - and not having starts-with(), ends-with(), and several other functions really drove me nuts). It will be crucial to be able to index on nodes using such functions quickly, so I can see how concentrating an initial effort on the XPath node extraction support will produce a better native XML database in the future.

I'm sorry that my initial reaction was so negative. I wasn't so negative when UTF-8 support started coming through with some problems at first, as I knew I wanted better internationalization support than the ujis (EUC-JP) default I used to compile dating back to starting with MySQL on FreeBSD 2.2.6. I now have tables that support all of the Asian character sets, sometimes within the same text field/record.

ExtractValue() and UpdateXML() appear to be the only two functions to use XPath. What about using XPath to get a subtree? That wasn't discussed in the article. Seeing something like:

SELECT xmlquery('$root/path/to[@attr="value"]/node' PASSING fieldname AS "root") FROM table;

to get a new XML document (using the JDBC 2.0 XML type for Java users) would really show that this is going in the right direction.

2008-02-18 05:40:43
Thanks a lot! I’m a beginner, and the learning process is very painful.
But you help to add clarity to the subject. I like that.