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!

Solution 1

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

Solution 2

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)

Solution 3

Below is a stored procedure example to read xml using a while loop

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

1 Status

2 Reason

3 User Remarks

Comments

[reticent]

Using this query I only get two rows. Am I missing something?

[Quassnoi]

@reticent: as was told before, MySQL lacks a rowset generation function. The subquery only returns 2 rows which is what you get. Add more rows into vars (or replace it with a SELECT from a table large enough).