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 .
I think the ramifications for this are actually quite huge. I’ve known for some time that much of the driving technology behind “Web 2.0″ is the power of SQL databases, with the bulk of those to date being mySQL databases. While enterprise level databases such as Oracle 10i+, IBM db2, and Microsoft SQL Server have long had XML capabilities, they also account collectively for a surprisingly small amount of the outward facing databases on the web, especially compared to mySQL. However, this has also has the unfortunate effect of promoting a relational database model as the prime one for the web, diminishing the utility of XML there and increasing the fragility of Web 2.0 applications.
With native XML support moving into mySQL, it opens up a chance for XML developers to start working within that community, and and also raises some significant issues with regard to how unstructured and semi-structured data is stored, retrieved and manipulated.
The XML support for mySQL is not yet at the level where it can support XQuery, but I think that this will come in time given the degree of support they have for the XPath specification. Keep an eye on this development; I think it will strengthen XML applications in general.
Thanks to Bill GIbbens of Influency for the initial heads up about the article.
Great news for the "full XML stack" junkies: RIAs favour XML technologies throughout all Web Application Tiers..?
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.)
wbs,
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.
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.
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.