如何在Oracle数据库中查询解析XMLType列并生成指定命名列

注意：你提供的XML存在格式错误，<c18 m="4"D</c18>缺少属性闭合的>符号，需要修正为<c18 m="4">D</c18>才能正常解析。

下面提供两种可行的SQL查询方案：

方案一：XMLTable展开+条件聚合转列 #

先将XML中的所有<c18>节点解析为行数据，再通过编号和条件聚合转换为目标列：

WITH xml_data AS (
    -- 替换为你的实际表和列
    SELECT Test
    FROM your_table
),
parsed_rows AS (
    SELECT 
        -- 按节点顺序编号：无m属性的是第1个，m=2到8依次为第2到8个
        CASE 
            WHEN x.m IS NULL THEN 1 
            ELSE TO_NUMBER(x.m) 
        END AS col_num,
        x.node_val AS value
    FROM xml_data,
         XMLTable('//c18'
             PASSING xml_data.Test
             COLUMNS
                 m VARCHAR2(5) PATH '@m',
                 node_val VARCHAR2(1) PATH '.'
         ) x
)
SELECT
    MAX(CASE WHEN col_num = 1 THEN value END) AS C1,
    MAX(CASE WHEN col_num = 2 THEN value END) AS C2,
    MAX(CASE WHEN col_num = 3 THEN value END) AS C3,
    MAX(CASE WHEN col_num = 4 THEN value END) AS C4,
    MAX(CASE WHEN col_num = 5 THEN value END) AS C5,
    MAX(CASE WHEN col_num = 6 THEN value END) AS C6,
    MAX(CASE WHEN col_num = 7 THEN value END) AS C7,
    MAX(CASE WHEN col_num = 8 THEN value END) AS C8
FROM parsed_rows;

方案二：直接用XPath定位提取 #

通过XPath精准匹配每个<c18>节点，直接提取对应值作为列：

SELECT
    -- 匹配无m属性的第一个<c18>
    Test.extract('//c18[not(@m)]/text()').getStringVal() AS C1,
    -- 匹配m属性为2到8的节点
    Test.extract('//c18[@m="2"]/text()').getStringVal() AS C2,
    Test.extract('//c18[@m="3"]/text()').getStringVal() AS C3,
    Test.extract('//c18[@m="4"]/text()').getStringVal() AS C4,
    Test.extract('//c18[@m="5"]/text()').getStringVal() AS C5,
    Test.extract('//c18[@m="6"]/text()').getStringVal() AS C6,
    Test.extract('//c18[@m="7"]/text()').getStringVal() AS C7,
    Test.extract('//c18[@m="8"]/text()').getStringVal() AS C8
FROM your_table;

说明：将上述代码中的your_table替换为你的实际表名即可执行。

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