Mysql – Parse an XML string in MySQL

MySQLxmlxpath

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!

Best Answer

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)