Bug #66299 Import xml with short tags into mysql
Submitted: 10 Aug 2012 7:36 Modified: 18 Aug 2015 16:44
Reporter: Weibang Tao Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: XML functions Severity:S4 (Feature request)
Version:5.5.27.1, 5.5.28 OS:Any
Assigned to: CPU Architecture:Any

[10 Aug 2012 7:36] Weibang Tao
Description:

I need to load some xml files into a mysql database.. I did it using LOAD XML and it works fine.. but sometimes I have some xml with short code in it and it did not work...
 

How to repeat:
load xml local infile 'D:\\file_xml.xml' into table book rows identified by '<Book>'

and I have a book table with ID, TITLE, PRICE columns..

If the xml is like:
<Books> 
  <Book> 
    <ID>1</ID> 
    <TITLE>Test1</TITLE> 
    <PRICE></PRICE> 
  </Book> 
  <Book> 
    <ID>2</ID> 
    <TITLE>Test2</TITLE> 
    <PRICE>123</PRICE> 
  </Book> 
</Books> 

 The above table is filled correctly...
ID - TITLE - PRICE 
1  - Test1 -  
2  - Test2 - 123 

 Otherwise if I have an xml file like:
<Books> 
  <Book> 
    <ID>1</ID> 
    <TITLE>Test1</TITLE> 
    <PRICE /> 
  </Book> 
  <Book> 
    <ID>2</ID> 
    <TITLE /> 
    <PRICE>123</PRICE> 
  </Book> 
</Books> 

 The table is not filled properly and I will have
ID - TITLE - PRICE 
1  - Test1 -  
2  - 123   -
[19 Aug 2012 16:25] Valeriy Kravchuk
I'd say this is a bug. Verified with 5.5.28 on Mac OS X:

...
macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.28-debug-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> load xml infile '/tmp/t2.xml' into table book rows identified by '<Book>';
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from book;
+------+-------+-------+
| ID   | TITLE | PRICE |
+------+-------+-------+
| 1    | Test1 | NULL  |
| 2    | Test2 | 123   |
| 1    | Test1 | NULL  |
| 2    | 123   | NULL  |
+------+-------+-------+
4 rows in set (0.01 sec)
[18 Aug 2015 16:44] Jon Stephens
Duplicate of BUG#67542.