如何将包含多层嵌套子节点的XML文件完整导入MySQL对应数据表

你当前的写法只能拿到单个节点的原因有两个：

• LOAD DATA LOCAL INFILE 按<product>到</product>拆分每行数据，一个product只会生成一行待插入记录，就算一个product下有多个area，也只会执行一次插入操作
• ExtractValue 匹配到多个节点时，默认只返回第一个匹配到的节点内容

首先第一步先修正XML文件：在所有内容的最外层加<root>和</root>标签，否则MySQL的XML解析器会因为多根节点报错。
如果执行时提示本地文件读取权限问题，先执行语句开启权限：SET GLOBAL local_infile = 1;
之后执行如下存储过程代码即可完成导入：

DELIMITER //
CREATE PROCEDURE import_areas()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total_area INT;
    -- 读取整个XML文件内容到临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS xml_raw (content LONGTEXT);
    LOAD DATA LOCAL INFILE '/var/lib/mysql/allprintdatafile_esp.xml' 
    INTO TABLE xml_raw 
    CHARACTER SET utf8;
    SET @xml = (SELECT content FROM xml_raw);
    -- 统计总共有多少个area节点
    SET total_area = ExtractValue(@xml, 'count(//area)');
    -- 循环插入每个area对应的数据
    WHILE i <= total_area DO
        INSERT INTO areas (id_area, id_print_job, id_product, areaname)
        VALUES (
            ExtractValue(@xml, CONCAT('//area[',i,']/areacode')),
            ExtractValue(@xml, CONCAT('//area[',i,']/../../teccode')),
            ExtractValue(@xml, CONCAT('//area[',i,']/../../../../ref')),
            TRIM(ExtractValue(@xml, CONCAT('//area[',i,']/areaname')))
        );
        SET i = i + 1;
    END WHILE;
    DROP TEMPORARY TABLE xml_raw;
END //
DELIMITER ;

-- 执行存储过程完成导入
CALL import_areas();

如果要导入product、printjob表，只需要调整遍历的节点和对应的XPath取值逻辑即可。

如果熟悉Python等脚本语言，可以先解析XML提取所有需要的字段再批量插入数据库，不需要处理MySQL的XML语法兼容问题，示例逻辑如下：

import xml.etree.ElementTree as ET
import pymysql

# 解析XML文件
tree = ET.parse('allprintdatafile_esp.xml')
root = tree.getroot()
data_list = []

for product in root.findall('product'):
    product_ref = product.find('ref').text
    for printjob in product.find('printjobs').findall('printjob'):
        teccode = printjob.find('teccode').text
        for area in printjob.find('areas').findall('area'):
            areacode = area.find('areacode').text
            areaname = area.find('areaname').text.strip()
            data_list.append((areacode, teccode, product_ref, areaname))

# 批量插入数据库
conn = pymysql.connect(host='localhost', user='你的数据库账号', password='你的数据库密码', database='你的库名', charset='utf8')
cursor = conn.cursor()
cursor.executemany("INSERT INTO areas (id_area, id_print_job, id_product, areaname) VALUES (%s, %s, %s, %s)", data_list)
conn.commit()
conn.close()

注：你给出的预期结果中第三行的areaname属于手误，原XML对应第三个printjob的areaname为Esquina inferior derecha de la portada，上述两种方法都会自动匹配正确的内容。

内容的提问来源于stack exchange，提问作者rues_12