How to LOAD XML in MySQL with self-closing tags?



I'm trying to load some XML to a MySQL database, but I encountered a problem with self-closing tags. A sample SQL to demonstrate my problem:



CREATE TEMPORARY TABLE `person` (
`name` VARCHAR(75)
);

CREATE TEMPORARY TABLE `individual` LIKE `person`;

LOAD XML INFILE 'document.xml'
INTO TABLE `individual`
ROWS IDENTIFIED BY '<individual>';

LOAD XML INFILE 'document.xml'
INTO TABLE `person`
ROWS IDENTIFIED BY '<person>';


When self-closing tags are followed by an element with a start and end tag, the load works as expected:



<?xml version="1.0"?>
<document>
<individual name="John Smith"></individual>
<person name="Joe Doe" />
<person name="Jane Doe" />
</document>


SELECT * FROM `individual`;
+------------+
| name |
+------------+
| John Smith |
+------------+
1 row in set
SELECT * FROM `person`;
+----------+
| name |
+----------+
| Joe Doe |
| Jane Doe |
+----------+
2 rows in set


But after one element represented with self-closing tags, the following ones are not loaded:



<?xml version="1.0"?>
<document>
<individual name="John Smith" />
<person name="Joe Doe" />
<person name="Jane Doe" />
</document>


SELECT * FROM `individual`;
+------------+
| name |
+------------+
| John Smith |
+------------+
1 row in set
SELECT * FROM `person`;
Empty set


How do I load an XML file with multiple self-closing tags into MySQL?


No comments:

Post a Comment