MySql查询检索xml元素属性的值 [英] MySql query to retrieve value of element attribute of xml

问题描述

我有一个 mysql 表(比如 TestSuite)，在 TestSuiteDefinition 列中保存 xml 内容(虽然是长文本)，

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>

现在，我想检索属性的值(在本例中为id").我知道如何在 MS SQL 中做到这一点，例如:

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)') 作为名称 FROM TestSuite WHERE TestSuiteId='368'

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

但无法在 MySQL 中弄清楚.注意:尝试过 MySQL 函数 ExtractValue() 但在检索元素属性时没有成功.谢谢

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

推荐答案

$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());

输出:

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

)

这篇关于MySql查询检索xml元素属性的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！