How to use LOAD XML INFILE to sort out duplicate tags and drive them into Mysql?

Question

PHP

Hey!
There is a large xml file (500-900MB, 100-500 thousand lines). You need to import it into MySQL.
1. First I tried via XMLReader(). But it's not fast enough.
2. Then I learned about the LOAD XML INFILE function. The speed is great. But there was a problem, I will give an example of the structure of the xml file:

<items>
<item param1="val1">
   <name>name_val</name>   
   <photo>photo1_val</photo>
   <photo>photo1_val</photo>
   <photo>photo3_val</photo>
</item>
</items>

I use this query:

LOAD XML INFILE '/file.xml'
INTO TABLE item
ROWS IDENTIFIED BY '<item>'

All fields and parameters enter the database correctly, except for 'photo'. Only the value of the last block 'photo3_val' gets into the database.
I try this, but this code doesn't work:

LOAD DATA INFILE '/file.xml'
INTO TABLE item 
CHARACTER SET 'utf8'
LINES STARTING BY '<item>' TERMINATED BY '<\item>'
(@tmp)
SET 
photo1 = ExtractValue(@tmp, '/photo[0]'),
photo2 = ExtractValue(@tmp, '/photo[1]'),
photo3 = ExtractValue(@tmp, '/photo[2]')

Please point me in the right direction.
Thank you!

Answer 1

Try the procedure with the following code:

DECLARE size INT UNSIGNED DEFAULT ExtractingValue(xml_file, "count(//item)");
DECLARE counter INT UNSIGNED DEFAULT 1;

WHILE counter <= size DO
    INSERT INTO your_table
    VALUES
    (
    ExtractValue(xml_file, "//item[$counter]/name"),
    ExtractValue(xml_file, "//item[$counter]/photo[1]"), #элементы нумеруются с 1, а не с 0; 
    ExtractValue(xml_file, "//item[$counter]/photo[2]") #и так для всех остальных фото
    );
    SET counter = counter + 1;
END WHILE;