Select query from XML string in MySQL

Question

I have XML string which having multiple child nodes, i want to fetch data from child nodes below is an example structure, i want to fetch all result nodes. Trying to use this query in MySQL

SET @xml= '<aaa><result>
        <id>1</id>
        </result>
        <result>
        <id>2</id>
        </result>
         ....
    </aaa>';
SELECT  ExtractValue(@xml, '/aaa/result/id/text()[1]');

Current output

1 2

Expected output

1
2

Let me know if there is best way to achieve this?

Rohan Khude · Accepted Answer · 2016-11-26 14:10:36Z

1

I did it by splitting the column into two columns

SET @xml= '<aaa><result>
        <id>1</id>
        </result>
        <result>
        <id>2</id>
        </result>
    </aaa>';

SELECT
        SUBSTRING_INDEX(ExtractValue(@xml, '//id'), ' ', 1) AS `id1`,
        SUBSTRING_INDEX(ExtractValue(@xml, '//id'), ' ', -1) AS `id2`

OUTPUT

id1   |    id2
1     |     2

As you said results node can be multiple, for this I have tried below query

SET @xml= '<aaa><result>
        <id>1</id>
        </result>
        <result>
        <id>2</id>
        </result>
        <result>
        <id>3</id>
        </result>
        <result>
        <id>4</id>
        </result>
        <result>
        <id>5</id>
        </result>
        <result>
        <id>7</id>
        </result>
        <result>
        <id>6</id>
        </result>
        <result>
        <id>8</id>
        </result>
        <result>
        <id>9</id>
        </result>
        <result>
        <id>10</id>
        </result>
        <result>
        <id>11</id>
        </result>        
    </aaa>';

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ExtractValue(@xml, '//id'), ' ', n.n), ' ', -1) value
  FROM (select SUBSTRING_INDEX(ExtractValue(@xml, '//id'), ' ', 1)) t CROSS JOIN 
  (
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n 
   ) n
 WHERE n.n <= 1 + (LENGTH(ExtractValue(@xml, '//id')) - LENGTH(REPLACE(ExtractValue(@xml, '//id'), ' ', '')))

OUTPUT

edited Nov 26, 2016 at 14:10

answered Nov 26, 2016 at 13:06

Rohan Khude

5,0015 gold badges52 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Suresh Over a year ago

Thanks Rohan for reply, unfortunately above solution will not work because result nodes can be multiple.

Rohan Khude Over a year ago

@Suresh I have updated the query for multiple nodes. Happy to help. If this answer or any other one solved your issue, please mark it as accepted.

Suresh Over a year ago

Yes Rohan updated query looks okay, i tried another solution used while loop based on ExtractValue(v_order_xml,'count(//v_order_date)') , and fetched data row by row using loop counter variable. Anyway accepting your answer. Thanks

Rohan Khude Over a year ago

@Suresh, Please accept the answer, still you dint accepted the answer. :-)

Collectives™ on Stack Overflow

Select query from XML string in MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related