如何用MySQL EXTRACTVALUE批量提取XML中所有Role的键值对

问题背景 #

现有如下XML数据：

<Attributes>
   <Map>
     <entry key="Role_111">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_13">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_16">
       <value>
         <List>
           <String>07770</String>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_37">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="Role_5">
       <value>
         <List>
           <String>07771</String>
         </List>
       </value>
     </entry>
     <entry key="isValid" value="true"/>
     <entry key="usercode" value="001"/>
   </Map>
</Attributes>

需要提取所有以Role_开头的entry节点的key和对应value下的String值，要求每个角色的键值对按不同行展示（单个角色有多个String值时也要分行）。目前仅能通过指定具体角色的EXTRACTVALUE语句获取值，无法批量提取所有角色。

可行解决方案 #

方法1：使用XMLTABLE（推荐，适用于Oracle 11g及以上） #

利用XMLTABLE解析XML，配合XPath筛选目标节点，同时拆分多值的String：

SELECT 
    xt.role_key,
    xt.role_value
FROM 
    your_table s1,
    XMLTABLE(
        '/Attributes/Map/entry[starts-with(@key, "Role_")]'
        PASSING s1.attributes
        COLUMNS 
            role_key VARCHAR2(50) PATH '@key',
            role_values XMLTYPE PATH 'value/List'
    ) xt1,
    XMLTABLE(
        '/List/String'
        PASSING xt1.role_values
        COLUMNS 
            role_value VARCHAR2(50) PATH '.'
    ) xt;

• 第一层XMLTABLE筛选所有Role_开头的entry，提取角色key和对应的List节点；
• 第二层XMLTABLE拆分每个List下的String值，实现单个角色多值时分行展示。

方法2：递归查询+EXTRACTVALUE（兼容低版本Oracle） #

如果无法使用XMLTABLE，可通过递归生成节点索引，逐个提取角色：

WITH role_entries AS (
    SELECT 
        EXTRACTVALUE(s1.attributes, '/Attributes/Map/entry[starts-with(@key, "Role_")][' || LEVEL || ']/@key') AS role_key,
        EXTRACTVALUE(s1.attributes, '/Attributes/Map/entry[starts-with(@key, "Role_")][' || LEVEL || ']/value/List/String') AS role_values
    FROM 
        your_table s1
    CONNECT BY 
        LEVEL <= EXTRACTVALUE(s1.attributes, 'count(/Attributes/Map/entry[starts-with(@key, "Role_")])')
)
SELECT 
    re.role_key,
    REGEXP_SUBSTR(re.role_values, '[^ ]+', 1, COLUMN_VALUE) AS role_value
FROM 
    role_entries re,
    TABLE(CAST(MULTISET(
        SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(re.role_values, '[^ ]+')
    ) AS SYS.ODCINUMBERLIST))
WHERE 
    re.role_key IS NOT NULL;

• 递归CTErole_entries先提取所有Role_开头的角色键和对应多值（EXTRACTVALUE会将多个String用空格分隔）；
• 再通过REGEXP_SUBSTR拆分多值，实现分行展示。

注意事项 #

• 替换your_table为实际表名，s1.attributes为存储XML的实际字段；
• 若使用其他数据库（如MySQL、SQL Server），语法会有差异，但核心思路均为：先筛选目标节点，再拆分多值。

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