在MySQL中解析XML字符串 [英] Parse an XML string in MySQL

问题描述

我的任务是解析MySQL存储过程中的简单XML格式的字符串. XML看起来像这样(仅用于测试目的):

I have a task of parsing a simple XML-formatted string in a MySQL stored procedure. XML looks like this (testing purposes only):

<parent>
    <child>Example 1</child>
    <child>Example 2</child>
</parent>

我需要MySQL做的是产生一个结果集，每次匹配一行.我存储的proc代码如下所示:

What I need MySQL to do is to produce a result set one row per match. My stored proc code looks like this:

DECLARE xmlDoc TEXT;
SET xmlDoc = '<parent><child>Example 1</child><child>Example 2</child></parent>';
SELECT ExtractValue(xmlDoc, '//child');

但是，这是将所有匹配项连接起来，生成示例1示例2".顺便说一下，这是有据可查的，但却是毫无用处的行为.

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.

我该怎么做才能使它按行返回匹配项，而不必计算匹配项并一一处理它们? MySQL甚至有可能吗?

What can I do to make it return the matches in rows without having to count the matches and processing them one-by-one? Is it even possible with MySQL?

谢谢！

推荐答案

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

或者...

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

很抱歉，这里的语法有点动摇，不是很多mysql专家...

Sorry if the syntax is a bit shakey in here, not much of a mysql guru...

这篇关于在MySQL中解析XML字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！