在Oracle数据库中转换含多项序列的XML为行列结构

嘿，我来帮你搞定Oracle里把XML多重复节点转成常规行列表的问题！你说单节点处理没问题，但碰到这种重复出现的序列字段就卡壳了对吧？我给你拆解下解决方案，直接上能用的SQL，再给你讲明白逻辑~

Oracle的XMLTable函数就是干这个的——能把XML里的重复节点拆成单独的行，同时还能保留同一父节点（比如你的）里的其他单值字段（c1、c2这些）。

1. 针对XMLType字段的查询（推荐） #

假设你的XML数据存在一个表（比如叫your_table）的XMLType字段xml_content里，直接用下面的SQL就能搞定：

SELECT 
    x.row_id,
    x.c1_value,
    x.c2_value,
    c3.c3_value,
    -- 给无m属性的c3补默认值，比如默认是m='1'
    NVL(c3.c3_m_attr, '1') AS c3_m_attr
FROM 
    your_table yt,
    -- 第一步：解析<row>节点，提取单值字段和所有c3节点的集合
    XMLTable(
        '/row'
        PASSING yt.xml_content
        COLUMNS 
            row_id VARCHAR2(100) PATH '@id', -- 提取row的id属性
            c1_value VARCHAR2(8) PATH 'c1',  -- 提取单值c1
            c2_value VARCHAR2(8) PATH 'c2',  -- 提取单值c2
            c3_nodes XMLType PATH './c3'    -- 把所有c3节点打包成XML片段
    ) x,
    -- 第二步：把c3节点集合拆成单独的行
    XMLTable(
        '/c3'
        PASSING x.c3_nodes
        COLUMNS 
            c3_value VARCHAR2(8) PATH '.',   -- 提取c3的文本值
            c3_m_attr VARCHAR2(10) PATH '@m' -- 提取c3的m属性
    ) c3;

2. 针对字符串存储的XML #

如果你的XML是存在VARCHAR2字段（比如xml_string_col）里，只需要多一步把字符串转成XMLType就行：

SELECT 
    x.row_id,
    x.c1_value,
    x.c2_value,
    c3.c3_value,
    NVL(c3.c3_m_attr, '1') AS c3_m_attr
FROM 
    your_table yt,
    XMLTable(
        '/row'
        PASSING XMLType(yt.xml_string_col) -- 把字符串转成XMLType
        COLUMNS 
            row_id VARCHAR2(100) PATH '@id',
            c1_value VARCHAR2(8) PATH 'c1',
            c2_value VARCHAR2(8) PATH 'c2',
            c3_nodes XMLType PATH './c3'
    ) x,
    XMLTable(
        '/c3'
        PASSING x.c3_nodes
        COLUMNS 
            c3_value VARCHAR2(8) PATH '.',
            c3_m_attr VARCHAR2(10) PATH '@m'
    ) c3;

3. 直接在SQL Developer里测试示例 #

你可以用这个带示例XML的SQL直接在SQL Developer里跑，验证效果：

WITH sample_xml AS (
    SELECT XMLType('<row id="1129040398101-20150630" xml:space="preserve">
        <c1>20150601</c1>
        <c2>20150630</c2>
        <c3>20150601</c3>
        <c3 m="2">20150601</c3>
        <c3 m="3">20150623</c3>
    </row>') AS xml_content FROM dual
)
SELECT 
    x.row_id,
    x.c1_value,
    x.c2_value,
    c3.c3_value,
    NVL(c3.c3_m_attr, '1') AS c3_m_attr
FROM 
    sample_xml sx,
    XMLTable(
        '/row'
        PASSING sx.xml_content
        COLUMNS 
            row_id VARCHAR2(100) PATH '@id',
            c1_value VARCHAR2(8) PATH 'c1',
            c2_value VARCHAR2(8) PATH 'c2',
            c3_nodes XMLType PATH './c3'
    ) x,
    XMLTable(
        '/c3'
        PASSING x.c3_nodes
        COLUMNS 
            c3_value VARCHAR2(8) PATH '.',
            c3_m_attr VARCHAR2(10) PATH '@m'
    ) c3;

跑出来会得到3行结果，每行对应一个节点，同时保留了row的id、c1、c2的值，完全是你要的常规行结构~

额外注意点 #

• 如果的文本是日期格式，可以把VARCHAR2(8)改成DATE，再用TO_DATE(c3_value, 'YYYYMMDD')转换格式。
• 要是你的XML带命名空间，记得在XMLTable里加上XMLNAMESPACES子句，比如XMLTable(XMLNAMESPACES('http://xxx.com' AS "ns"), '/ns:row' ...)。

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