LOAD XML with bit fields in MySql

Question

I Have the following table:

CREATE TABLE `tmp_table` ( 
`id` int(11) NOT NULL AUTO_INCREMENT, 
`t` bit(1) NOT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1$$ 

And an xml file called "data.xml" that contains 1 line:

<list><row t="0" /></list> 

When I run the following command:

LOAD XML LOCAL INFILE 'c:/temp/data.xml' INTO TABLE `tmp_table` 

After running this command I get one row with a value of "1" for column t and a warning:

LOAD XML LOCAL INFILE 'c:/temp/data.xml' INTO TABLE `tmp_table` 1 row(s) affected, 1 warning(s): 
1264 Out of range value for column 't' at row 1 
Records: 1 Deleted: 0 Skipped: 0 Warnings: 1    0.000 sec 

How can I load a 0 for a bit field in an xml document?

Answer 1

MySQL suggests to do next:

BIT values cannot be loaded using binary notation (for example, b'011010'). To work around this, specify the values as regular integers and use the SET clause to convert them so that MySQL performs a numeric type conversion and loads them into the BIT column properly:

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

I have tried this query:

LOAD XML LOCAL INFILE 'data.xml' INTO TABLE `tmp_table`
  ROWS IDENTIFIED BY '<row>'
  (@var1)
  SET t = CAST(@var1 AS SIGNED);

...and I got stange warning message - 'Column 't' cannot be null'.

Hope this will work for you; otherwise, I think, you should write a request to bugs.mysql.com

Answer 2

LOAD XML INFILE seems to be not that good at importing data from arbitrary XML.

I have a blog post about using LOAD DATA INFILE to import from XML. since the approach uses a user-defined variable to hold the group, you can add an additional function to cast the value.

Alternatively, you can try to export data from MySQL in XML, look at how it represents bit values and adjust your xml before loading with XSLT.

My post was actually inspired by the question: LOAD XML LOCAL INFILE with Inconsistent Column Names