Description:
When loading an XML file using LOAD XML INFILE the loader will choose the last XML element which has a name matched to the tables column name within the XML, even if that element is a nested child node, and not the one that exists at the tag level.
This XML will result in the column name fname populated with AmIABug? when Likame was expected.
<person person_id="9"><fname>Likame</fname><lname>Örrtmons</lname><child><fname>AmIABug?</fname></child></person>
The depth of the nesting doesn't appear to make any difference, the loader selects the last found matching element.
How to repeat:
Following the example on the https://dev.mysql.com/doc/refman/9.0/en/load-xml.html page:
Create an XML file as per the man page above, with 1 extra entry (9) as shown below, and save it into the servers Upload folder:
<list>
<person person_id="1" fname="Kapek" lname="Sainnouine"/>
<person person_id="2" fname="Sajon" lname="Rondela"/>
<person person_id="3"><fname>Likame</fname><lname>Örrtmons</lname></person>
<person person_id="4"><fname>Slar</fname><lname>Manlanth</lname></person>
<person><field name="person_id">5</field><field name="fname">Stoma</field>
<field name="lname">Milu</field></person>
<person><field name="person_id">6</field><field name="fname">Nirtam</field>
<field name="lname">Sklöd</field></person>
<person person_id="7"><fname>Sungam</fname><lname>Dulbåd</lname></person>
<person person_id="8" fname="Sraref" lname="Encmelt"/>
<person person_id="9"><fname>Likame</fname><lname>Örrtmons</lname><child><fname>AmIABug?</fname></child></person>
</list>
Create a table:
CREATE TABLE person (
person_id INT NOT NULL PRIMARY KEY,
fname VARCHAR(40) NULL,
lname VARCHAR(40) NULL,
created TIMESTAMP
);
Execute the LOAD XML command (with the file location as per your server installation):
LOAD XML INFILE 'D:/ProgramData/MySQL/MySQL Server 9.0/Uploads/person.xml'
INTO TABLE person
ROWS IDENTIFIED BY '<person>';
commit;
Check the results for person_id 9:
Actual:
mysql> SELECT * FROM person where person_id = 9;
+-----------+----------+----------+---------+
| person_id | fname | lname | created |
+-----------+----------+----------+---------+
| 9 | AmIABug? | Örrtmons | NULL |
+-----------+----------+----------+---------+
1 row in set (0.00 sec)
Expected:
mysql> SELECT * FROM person where person_id = 9;
+-----------+----------+----------+---------+
| person_id | fname | lname | created |
+-----------+----------+----------+---------+
| 9 | Likame | Örrtmons | NULL |
+-----------+----------+----------+---------+
1 row in set (0.00 sec)
Suggested fix:
Either:
Change the LOAD XML processor to match column names to elements that are at the same level as the tag from ROWS IDENTIFIED BY.
Or:
Change the LOAD XML processor to choose the matched column names to elements by the depth that they are away from the tag from ROWS IDENTIFIED BY, such that those at the same level are chosen if present, then down through the nested XML.
