我有以下xml
<EmployeeList>
<Employee>
<EmpID>11</EmpID>
<EmpName>ABC</EmpName>
</Employee>
<Employee>
<EmpID>12</EmpID>
<EmpName>XYZ</EmpName>
</Employee>
</EmployeeList>
我想在to Employee表中插入基于Employee计数的元素值
我有一个employee表,它的id和name作为列。
下面是代码片段
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]'))
);
end while;
以上仅适用于属性值。
到目前为止,我所做的是:
-- 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, ']/@*');
set @I=1,@j=2;
insert into applicants values (
extractValue(xml_content, '//Employee[$@i]'),
extractValue(xml_content, '//Employee[$@j]')
);
end while;
我要做的是:
我有一个如上所述的xml。
里面有两份员工记录。
要在Employee表中插入这些
最佳答案
您可以创建一个过程,如图所示,从XML读取并插入到表中:
DELIMITER $$
CREATE PROCEDURE `import_applicant_xml`()
BEGIN
declare path varchar(255);
declare node varchar(255);
declare xml_content text;
declare v_row_index int unsigned default 0;
declare v_row_count int unsigned;
declare v_empID varchar(255);
declare v_empName varchar(255);
set path = 'C:\\demo\\employee.xml';
set node = 'Employee';
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_empID = concat('//',node, '[', v_row_index, ']/EmpID');
set v_empName = concat('//',node, '[', v_row_index, ']/EmpName');
insert into employee values (
extractValue(xml_content,v_empID),
extractValue(xml_content, v_empName)
);
end while;
END
可以将路径和节点值作为
IN PARAMETERS
传递给过程,使其更具动态性。关于mysql - 使用extractValue从MySql中的元素获取值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/27985709/