Oracle使用XPath与XQuery拼接XML节点输出指定格式单行结果

核心规则对齐 #

首先明确节点位置匹配逻辑，完全对齐需求要求：

• <name>节点直接取文本值即可
• 其余三类节点（phone/sms/email）：无m属性的节点默认对应位置1，带m属性的节点位置为m的属性值
• 按位置从1到所有节点中最大的m值遍历，缺失对应位置节点时填空字符串，同组所有值用#拼接

可直接运行的查询语句 #

假设存储CLOB格式XML的表名为your_table，CLOB列名为xml_content，查询语句如下：

SELECT
    xt.name  AS "Name",
    xt.phone AS "Phone",
    xt.sms   AS "Sms",
    xt.email AS "Email"
FROM your_table t,
XMLTable(
    'let $root := /row
     let $max_pos := max((
         $root/phone/@m/number(),
         $root/sms/@m/number(),
         $root/email/@m/number(),
         1
     ))
     return <result>
         <name>{$root/name/text()}</name>
         <phone>{
             string-join(
                 for $pos in 1 to $max_pos
                 return string($root/phone[($pos = 1 and not(@m)) or @m/number() = $pos]/text()),
                 "#"
             )
         }</phone>
         <sms>{
             string-join(
                 for $pos in 1 to $max_pos
                 return string($root/sms[($pos = 1 and not(@m)) or @m/number() = $pos]/text()),
                 "#"
             )
         }</sms>
         <email>{
             string-join(
                 for $pos in 1 to $max_pos
                 return string($root/email[($pos = 1 and not(@m)) or @m/number() = $pos]/text()),
                 "#"
             )
         }</email>
     </result>'
    PASSING XMLType(t.xml_content)
    COLUMNS
        name  VARCHAR2(100) PATH 'name',
        phone VARCHAR2(200) PATH 'phone',
        sms   VARCHAR2(200) PATH 'sms',
        email VARCHAR2(200) PATH 'email'
) xt;

结果验证 #

针对给出的示例XML，上述语句返回结果完全匹配预期：

注意事项 #

• 若XML列本身就是XMLType类型，可去掉PASSING子句里的XMLType()转换，直接传入列名即可
• 语句自动适配最大位置序号，后续如果出现m='4'这类新增节点，不需要修改语句即可正常拼接
• 空节点（比如示例中<phone m='3'></phone>）会正确返回空字符串参与拼接

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