在MySQL中从XML字符串中选择查询

Question

我有一个XML字符串，它有多个子节点，我想从下面的子节点中获取数据，这是一个示例结构，我想获取所有的结果节点。试图在MySQL中使用此查询

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

电流输出

1 2

预期输出

1
2

让我知道是否有最好的方法来实现这一点？

Answer 1

我把这列分成两列。

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`

输出

id1   |    id2
1     |     2

正如您所说的，结果节点可以是多个，为此，我尝试了以下查询

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'), ' ', '')))

输出

Value

1
2
3
4
5
7
6
8
9
11