MySQL中extractValue提取XML多值如何实现分行展示？

问题场景 #

mytable表的attributes列存储如下XML数据：

<Attributes>
    <Map>
       <entry key="ABC">
          <value>
           <List>
              <String>12 3</String>          
              <String>4 56</String>    
           </List>
         </value>
       </entry>
    </Map>
</Attributes>

使用extractValue函数执行以下SQL时：

SELECT itemno, extractValue(attributes, '/Attributes/Map/entry[1]/value/List/String') 
as Value FROM mytable 

得到的结果是将所有节点值合并为一行：

itemno    Value
------    -----
1         12 3  4 56

需要实现每个节点值单独占一行，对应itemno重复输出。

解决方案 #

方法1：MySQL 8.0+ 推荐使用XML_TABLE函数 #

MySQL 8.0及以上版本支持XML_TABLE函数，可以直接将XML节点转换为行数据，语法简洁高效：

SELECT 
    t.itemno,
    x.value AS Value
FROM mytable t
CROSS JOIN XML_TABLE(
    t.attributes,
    '/Attributes/Map/entry[1]/value/List/String' COLUMNS(
        value VARCHAR(100) PATH '.'
    )
) x;

该语句通过CROSS JOIN将每个节点映射为单独一行，自动关联原表的itemno字段，输出结果符合预期。

方法2：MySQL 5.x 版本使用数字辅助表+extractValue #

MySQL 5.x版本不支持XML_TABLE，可以通过生成数字序列辅助提取每个位置的节点值：

-- 生成包含连续数字的临时表（示例生成1-10，可根据实际节点数量调整）
WITH nums AS (
    SELECT 1 AS n 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 UNION ALL SELECT 10
)
SELECT 
    t.itemno,
    extractValue(t.attributes, CONCAT('/Attributes/Map/entry[1]/value/List/String[', nums.n, ']')) AS Value
FROM mytable t
JOIN nums ON extractValue(t.attributes, CONCAT('/Attributes/Map/entry[1]/value/List/String[', nums.n, ']')) != '';

原理是通过数字序列对应XML中节点的位置，逐个提取值，并过滤掉空结果（当数字超过实际节点数量时，extractValue返回空字符串）。

注意事项 #

• extractValue函数在MySQL 8.0中已被标记为废弃，官方推荐使用XML_TABLE或JSON_TABLE（若XML可转换为JSON）替代。
• 数字辅助表的数字范围需覆盖节点的最大数量，避免遗漏数据。

内容的提问来源于stack exchange，提问作者Naveen