Problems with MySQL LOAD XML INFILE

Question

I have an XML document in the format ...

<?xml version="1.0" encoding="UTF-8"?> <yahootable> <row> <various><![CDATA[ multiline text, "&" other <stuff> ]]> </various> <id>1</id> <message><![CDATA[ sdfgsdfg dsfsdfsd ]]> </message> </row> <yahootable> 

... and want to use MySQL LOAD XML LOCAL INFILE to insert it into a table with columns; (various, id, message). I can't seem to get data from unpacked CDATA tags into database columns. Is it that the data between the CDATA tags is completely ignored or is there something I missed? I expected CDATA to simply escape from illegal XML characters and add it as plain text.

Thanks.

Answer 1

I could not find a way to do this using LOAD XML INFILE while preserving the contents of CDATA. However, the following works and uses the good old LOAD DATA INFILE along with ExtractValue() to achieve the same:

If you have an example file and this table:

 CREATE TABLE `yahootable` ( `id` int(11) NOT NULL PRIMARY KEY, `various` text, `message` text ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; 

then running this statement will import the contents of the file into the table:

 LOAD DATA INFILE '/tmp/yahootable.xml' INTO TABLE yahootable CHARACTER SET 'utf8' LINES STARTING BY '<row>' TERMINATED BY '</row>' (@tmp) SET id = ExtractValue(@tmp, '//id'), various = ExtractValue(@tmp, '//various'), message = ExtractValue(@tmp, '//message') ; 

This works by telling LOAD DATA INFILE that each <row>...</row> is a logical "string" that it stores in the local @tmp variable. Then we pass this to the ExtractValue function as an XML fragment and select the values ​​from them that we want using the appropriate XPath expressions.