It’s very nice that the LOAD XML patch finally got some attention. I wasn’t expecting it anymore. I worked on this with Erik a year ago. My C programming skill were to rusty at that time, that only my BNF code contribution was used and a lot of issues were never solved. With my C prog skills back up to a decent level, I;m planning to pick up the project. Now seems to be a better time than ever.

The current solution works well in certain situations, but definitely not in all. To insert referential data, you need to specify the id value at forehand, look at project_id in the example. That doesn’t seem like a real world scenario. You should be able to insert the whole XML tree at once creating the reference as you go. This unfortunately doesn’t fit in the current LOAD DATA scheme, on which LOAD XML is based, and therefor requires a lot of additional work.

The LOAD XML would also be really useful with todays Web 2.0/AJAX applications. There is no need anymore to convert the XML tree to INSERT queries in PHP (or some other script language). With the current implementation you are required to save the received values in a file, but working with lots temp files isn’t a nice solution. Much nicer would if there was an alternative syntax, as is with INSERT queries, where you use a SELECT query instead of INFILE.

LOAD XML INTO TABLE person ROWS IDENTIFIED BY '<person>' SELECT '<?xml version="1.0"?>
<list>
  <person fname="Pekka" lname="Nousiainen"/>
  <person><fname>Mikael</fname><lname>Ronström</lname></person>
</list>';
LOAD XML INTO TABLE article ROWS IDENTIFIED BY '<item>' SELECT * FROM rss_data;

Last it would be nice if you could specify an XPath statement as ROWS IDENTIFIED BY as well as the current syntax.

LOAD XML LOCAL INFILE 'person.xml' INTO TABLE person ROWS IDENTIFIED BY '//person[@group="friend"]'

Together with some of the libraries in the MySQL UDF Repository, you could really think up some really sweet solutions. For instance implementing an RSS aggregator (used for planet sites) would be really simple using the lib_mysqludf_curl library Tim Gustafson is working on.

LOAD XML IGNORE INTO TABLE article ROWS IDENTIFIED BY '<item>' SELECT curl_get(link AS url) FROM feed;

The world of MySQL lovers using XML and XML lovers using MySQL, just got a bit brighter. And I think if I can put in some work here, I can make it really shine.