在 MySQL 中解析 XML 字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/902678/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parse an XML string in MySQL
提问by MK_Dev
I have a task of parsing a simple XML-formatted string in a MySQL stored procedure. XML looks like this (testing purposes only):
我的任务是在 MySQL 存储过程中解析一个简单的 XML 格式的字符串。XML 看起来像这样（仅用于测试目的）：
<parent>
<child>Example 1</child>
<child>Example 2</child>
</parent>
What I need MySQL to do is to produce a result set one row per match. My stored proc code looks like this:
我需要 MySQL 做的是为每个匹配生成一个结果集。我存储的 proc 代码如下所示：
DECLARE xmlDoc TEXT;
SET xmlDoc = '<parent><child>Example 1</child><child>Example 2</child></parent>';
SELECT ExtractValue(xmlDoc, '//child');
What this does, however, is it concatenate all the matches, producing "Example 1 Example 2". This is, by the way, documented, but quite useless behavior.
然而，它的作用是连接所有匹配项，生成“示例 1 示例 2”。顺便说一下，这是有记录的，但非常无用的行为。
What can I do to make it return the matches in rowswithout having to count the matches and processing them one-by-one? Is it even possible with MySQL?
我该怎么做才能使它返回行中的匹配项，而不必计算匹配项并逐一处理它们？甚至可以使用 MySQL 吗？
Thanks all!
谢谢大家！
采纳答案by Tracker1
DECLARE i INT DEFAULT 1;
DECLARE count DEFAULT ExtractValue(xml, 'count(//child)');
WHILE i <= count DO
SELECT ExtractValue(xml, '//child[$i]');
SET i = i+1;
END WHILE
Alternatively...
或者...
DECLARE v VARCHAR(500) DEFAULT '';
DECLARE i INT DEFAULT 1;
REPEAT
SET v = ExtractValue(xml, '//child[$i]')
SET i = i+1;
IF v IS NOT NULL THEN
-- do something with v
END IF
UNTIL v IS NULL
Sorry if the syntax is a bit shakey in here, not much of a mysql guru...
对不起，如果这里的语法有点不稳定，不是 mysql 大师......
回答by Quassnoi
This needs a rowset generation function, and MySQLlacks it.
这需要一个行集生成函数，但MySQL缺少它。
You can use a dummy table or a subquery instead:
您可以改用虚拟表或子查询：
SELECT ExtractValue(@xml, '//mychild[$@r]'),
@r := @r + 1
FROM (
SELECT @r := 1
UNION ALL
SELECT 1
) vars
Note that support for session variables in XPathis broken in 5.2(but works fine in 5.1)
请注意，对会话变量的支持已XPath被破坏5.2（但在 中工作正常5.1）
回答by abksharma
Below is a stored procedure example to read xml using a while loop
下面是一个使用 while 循环读取 xml 的存储过程示例
-- drop procedure testabk;
-- call testabk();
delimiter //
create procedure testabk()
begin
DECLARE k INT UNSIGNED DEFAULT 0;
DECLARE xpath TEXT;
declare doc varchar(1000);
DECLARE row_count1 INT UNSIGNED;
set doc='<StaticAttributesBM><AttributeId id="11">Status</AttributeId><AttributeId id="2">Reason</AttributeId><AttributeId id="3">User Remarks</AttributeId></StaticAttributesBM>';
DROP TABLE IF EXISTS tempStaticKeywords;
CREATE TABLE tempStaticKeywords(id int, staticKeywords VARCHAR(500));
SET row_count1 := extractValue(doc,'count(/StaticAttributesBM/AttributeId)');
select row_count1;
-- iterate over books
WHILE k < row_count1 DO
SET k := k + 1;
SET xpath := concat('/StaticAttributesBM/AttributeId[', k, ']');
INSERT INTO tempStaticKeywords(id,staticKeywords) VALUES (
extractValue(doc, concat(xpath,'/@id')),
extractValue(doc, xpath)
);
END WHILE;
select * from tempStaticKeywords;
END
//
Output is below
输出低于
id staticKeywords
id 静态关键字
1 Status
1 状态
2 Reason
2 原因
3 User Remarks
3 用户备注
