Description:
Load xml infile reads only first of inline <tag .../> series data.
How to repeat:
-- xml file: --------------------
<?xml version="1.0"?>
<home>
<tag1 attr1="1"/>
<tag2 attr2="2"/>
</home>
-- xml file: --------------------
-- sql cmds: --------------------
create table if not exists home_tag1 (attr1 smallint);
create table if not exists home_tag2 (attr2 smallint);
load xml local infile 'home.xml'
into table home_tag1
rows identified by '<tag1>';
load xml local infile 'home.xml'
into table home_tag2
rows identified by '<tag2>';
-- sql cmds: --------------------
-- results: ---------------------
mysql> create table if not exists home_tag1 (attr1 smallint);
Query OK, 0 rows affected (0.01 sec)
mysql> create table if not exists home_tag2 (attr2 smallint);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> load xml local infile 'home.xml'
-> into table home_tag1
-> rows identified by '<tag1>';
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
mysql>
mysql> load xml local infile 'home.xml'
-> into table home_tag2
-> rows identified by '<tag2>';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Deleted: 0 Skipped: 0 Warnings: 0
-- results: ---------------------
Suggested fix:
Current workaround is altering the xml source data to comment or remove <tag1 ../> after reading it. Only after the first tag is removed can the second be read.
<?xml version="1.0"?>
<home>
<!--tag1 attr1="1"/-->
<tag2 attr2="2"/>
</home>
-- results ---
mysql> load xml local infile 'home.xml'
-> into table home_tag2
-> rows identified by '<tag2>';
Query OK, 1 row affected (0.00 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
Description: Load xml infile reads only first of inline <tag .../> series data. How to repeat: -- xml file: -------------------- <?xml version="1.0"?> <home> <tag1 attr1="1"/> <tag2 attr2="2"/> </home> -- xml file: -------------------- -- sql cmds: -------------------- create table if not exists home_tag1 (attr1 smallint); create table if not exists home_tag2 (attr2 smallint); load xml local infile 'home.xml' into table home_tag1 rows identified by '<tag1>'; load xml local infile 'home.xml' into table home_tag2 rows identified by '<tag2>'; -- sql cmds: -------------------- -- results: --------------------- mysql> create table if not exists home_tag1 (attr1 smallint); Query OK, 0 rows affected (0.01 sec) mysql> create table if not exists home_tag2 (attr2 smallint); Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> load xml local infile 'home.xml' -> into table home_tag1 -> rows identified by '<tag1>'; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 mysql> mysql> mysql> load xml local infile 'home.xml' -> into table home_tag2 -> rows identified by '<tag2>'; Query OK, 0 rows affected (0.00 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 -- results: --------------------- Suggested fix: Current workaround is altering the xml source data to comment or remove <tag1 ../> after reading it. Only after the first tag is removed can the second be read. <?xml version="1.0"?> <home> <!--tag1 attr1="1"/--> <tag2 attr2="2"/> </home> -- results --- mysql> load xml local infile 'home.xml' -> into table home_tag2 -> rows identified by '<tag2>'; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0