MySql query to retrieve value of element attribute of xml

Question

I have mysql table (say TestSuite), holding xml content (although as long text) in TestSuiteDefinition column,

 <test_suite id="368">
   <name>TestSuite1</name>
   <description>TestSuite</description>
   <test_case id="141" version="" />
   <test_case id="142" version="" />
   <test_case id="143" version="" />
   <test_case id="144" version="" />
</test_suite>

now, I want to retrieve the value of attributes ("id" in this case). I know how to do it in MS SQL e.g:

SELECT TestSuiteDefinition.query('data(/test_suite/test_case/@id)') as name FROM TestSuite WHERE TestSuiteId='368'

But not able to figure it out in MySQL. Note: Tried MySQL function ExtractValue() but no success on retrieving element attributes. Thanks

SELECT ExtractValue('<test_case @id></test_case>','/test_case[@id]') as name FROM TestSuite WHERE TestCaseId=''368" — Chirag Dhyani
– Chirag Dhyani, Commented Mar 30, 2015 at 9:56

mhall · Accepted Answer · 2015-03-30 10:42:51Z

2

$rows = $mysqli->query(<<<EOQ
    SELECT ExtractValue(TestSuiteDefinition,'//test_case/@id') as name
    FROM   TestSuite
    WHERE  TestCaseId=368
EOQ
) or die($mysqli->error);

print_r($rows->fetch_all());

Output:

Array
(
    [0] => Array
        (
            [0] => 141 142 143 144
        )

)

answered Mar 30, 2015 at 10:42

mhall

3,7113 gold badges26 silver badges35 bronze badges

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

Comments

Mike Miller · Accepted Answer · 2015-03-30 10:42:16Z

2

I haven't tested this but give this a try

SELECT ExtractValue(TestSuiteDefinition,'/test_case[1]/@id') as name FROM TestSuite WHERE TestCaseId=''368"

This will get you the first one back. You will need to do multiple ExtractValue calls to get each attribute back I think

edited Mar 30, 2015 at 10:42

answered Mar 30, 2015 at 10:11

Mike Miller

3,1273 gold badges27 silver badges32 bronze badges

2 Comments

Chirag Dhyani Over a year ago

Thank for the effort Mike. but here I am trying to retrieve attribute value instead of node values. So the query should return 141 142 143 144

Mike Miller Over a year ago

Updated the answer- also untested as I am on my phone but should be pretty close

Collectives™ on Stack Overflow

MySql query to retrieve value of element attribute of xml

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related