mysql - 想要解析 MySQL 中的 XML 文件(使用预定义的函数/过程)并将其插入到表中

Question

我在下面粘贴了一个 XML 文件。我想在 MySQL 中解析它。

1) 我提到了一些链接[1]，首先我们必须加载 XML 文件并将其插入表中。

[1] - https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

2) 我还读到使用 ExtractValue 函数获取值，但我得到的输出为 NULL

ExtractValue(@xml, -here-node-path);

这是 XML 文件:

<?xml version="1.0" encoding="utf-8"?>
<ItemData>
    <Rows>
        <VRow ID="ba3c4fd9-6691-49ee-996a-9841810d8264" ItemType="Pulse" />
        <VRow ID="401682df-9839-456e-b08f-563361392530" ItemType="Height" />
        <VRow ID="c39ee7ab-7217-4750-bc0d-9cec495fdd41" ItemType="Weight" />
        <VRow ID="effabbcb-718f-4b0c-8f81-6d0bf4ba5028" ItemType="BloodPressure" />
        <VRow ID="eb6451d3-646a-4447-919a-f778daf6fdc5" ItemType="BodyMassIndex" />
    </Rows>
    <Groups>
        <VGroup ID="4535bf31-da00-47e8-8975-f21a1b3fdb62" ReadingDate="2009-07-24T14:26:28.50Z">
            <Notes />
            <Readings>
                <VitalReading ID="af0af8e1-41d4-4cc9-a042-7a33876b643e" ItemType="Pulse">
                    <Values>
                        <ValueItem Type="{302DABB8-BF22-4da1-BE2F-8213F8A191D8}" ID="f46322d9-2e15-4542-ad33-d37395dfe31b" Initialized="True">
                            <Pulse>80</Pulse>
                        </ValueItem>
                    </Values>
                </VitalReading>                 
            </Readings>
        </VGroup>
    </Groups>
</ItemData>

有人可以建议我解决这个问题吗？

Answer 1

很可能您不知道每次运行将导入多少条记录。因此，行计数对于能够遍历每个 XML 行是必不可少的。在 while 循环中，我们必须将行索引递增 1 才能检索当前的 XML 行。可以使用方括号 [n] 获取特定行，其中 n 是 v_row_index。最后一个正斜杠后的 @* 告诉 ExtractValue() 获取节点的全部内容。然后，我们可以使用与行相同的方式访问每个属性，再次使用方括号 [n]。我在这里使用的 Insert 语句语法不包括列名。因此，每个参数都按顺序插入。

    declare v_row_index int unsigned default 0;   
    while v_row_index < v_row_count do                
        set v_row_index = v_row_index + 1;        
        set v_xpath_row = concat(node, '[', v_row_index, ']/@*');

        insert into applicants values (
            extractValue(xml_content, concat(v_xpath_row, '[1]')),
            extractValue(xml_content, concat(v_xpath_row, '[2]')),
            extractValue(xml_content, concat(v_xpath_row, '[3]'))
        );
    end while;
Here is the full proc code:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `import_applicant_xml`(path varchar(255), node varchar(255))
BEGIN
    declare xml_content text;
    declare v_row_index int unsigned default 0;   
    declare v_row_count int unsigned;  
    declare v_xpath_row varchar(255); 

    set xml_content = load_file(path);

    -- calculate the number of row elements.   
    set v_row_count  = extractValue(xml_content, concat('count(', node, ')')); 

    -- loop through all the row elements    
    while v_row_index < v_row_count do                
        set v_row_index = v_row_index + 1;        
        set v_xpath_row = concat(node, '[', v_row_index, ']/@*');
        insert into applicants values (
            extractValue(xml_content, concat(v_xpath_row, '[1]')),
            extractValue(xml_content, concat(v_xpath_row, '[2]')),
            extractValue(xml_content, concat(v_xpath_row, '[3]'))
        );
    end while;
END
Performing a Test Run

Calling the proc is just a matter of using the call command with the proc name and two input parameters.   (Remember to escape backslashes in the file path on Windows platforms.)

MySQL> call import_applicants_xml('C:\\applicants1.xml', '/applicant_list/applicant');
MySQL> select * from applicants;
+---+----------+-------------+
 |1  |Rob       |Gravelle     |
 +---+----------+-------------+
 |2  |Al        |Bundy        |
 +---+----------+-------------+
 |3  |Little    |Richard      |
 +---+----------+-------------+
 3 row(s) returned