Parse an XML string in MySQL

Question

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>

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');

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.

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?

Thanks all!

Answer 1

This needs a rowset generation function, and MySQL lacks it.

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 XPath is broken in 5.2 (but works fine in 5.1)

Answer 2

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...