mysql - 如何在 MySQL 存储过程中循环 XML 元素？

Question

MySQL 文档有这个示例:

mysql> DELIMITER |

mysql> CREATE PROCEDURE myproc ()
    -> BEGIN
    ->   DECLARE i INT DEFAULT 1;
    ->   DECLARE xml VARCHAR(25) DEFAULT '<a>X</a><a>Y</a><a>Z</a>';
    ->
    ->   WHILE i < 4 DO
    ->     SELECT xml, i, ExtractValue(xml, '//a[$i]');
    ->     SET i = i+1;
    ->   END WHILE;
    -> END |

有没有一种方法可以在不硬编码元素数量(本例中的值 4)的情况下实现此目的？

更新我正在寻找一个通用的解决方案。不要被问题中显示的特定 XML 分散注意力，它只是直接来自 MySQL 手册页的示例。

Answer 1

如果您有一个简单的 XML 结构，那么您可以使用递归 CTE 在单个查询中提取详细信息。以下仅在 MySQL 8 上进行了测试，因此我不能保证它是否适用于早期版本。

SET @xml =
'<objects>
    <object>
        <objectid>101</objectid>
        <objecttype>T1</objecttype>
    </object>
    <object>
        <objectid>102</objectid>
        <objecttype>T2</objecttype>
    </object>
    <object>
        <objectid>103</objectid>
        <objecttype>T3</objecttype>
    </object>
</objects>';

WITH RECURSIVE RowGenerator (SeqNum)
AS (
    SELECT 1
    
    UNION ALL
    
    SELECT SeqNum + 1
    FROM RowGenerator
    WHERE SeqNum < ExtractValue(@xml, 'count(/objects/object)')
)
SELECT @RowNum := SeqNum 
, ExtractValue(@xml, '/objects/object[$@RowNum]/objectid')
, ExtractValue(@xml, '/objects/object[$@RowNum]/objecttype')
FROM RowGenerator;