如何让ExtractValue SQL将XML节点多值返回为单独行？

原XML数据 #

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

原SQL语句 #

SELECT COALESCE(
                 ExtractValue(
                               attributes, 
                               '/Attributes/Map/entry[1]/value/List/String'), 
                'N/A'
                )
  FROM mytable

当前返回结果 #

12 3 4 56

需求 #

修改SQL，使每个<String>节点的内容单独占一行，即结果为两行：12 3和4 56

针对Oracle数据库（原SQL使用ExtractValue，属于Oracle函数），可以使用XMLTable函数拆分XML节点，实现每行返回一个<String>的值：

SELECT COALESCE(x.str_val, 'N/A') AS str_val
FROM mytable t,
     XMLTable('/Attributes/Map/entry[1]/value/List/String'
              PASSING t.attributes
              COLUMNS str_val VARCHAR2(100) PATH '.') x

说明 #

• XMLTable会遍历XPath表达式匹配到的所有<String>节点，每个节点对应一行结果
• PASSING子句指定要解析的XML列（这里是mytable表的attributes列）
• COLUMNS子句定义输出列的名称、数据类型，以及取值路径（.表示取当前节点的文本内容）
• 保留COALESCE函数，确保当没有匹配到任何<String>节点时返回N/A

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