Oracle通用动态函数：将任意嵌套XML扁平化转换为行列结构

核心逻辑 #

要实现任意层级XML的动态解析，核心是递归遍历XML所有节点，提取叶子节点的完整路径与对应值，再通过动态SQL将这些数据转成扁平的行列结构。Oracle XMLDB的原生函数结合递归逻辑、动态SQL可以实现这个通用能力，无需针对特定XML结构硬编码。

分步实现 #

1. 定义数据类型与递归节点提取函数 #

先创建存储节点信息的对象与集合类型，再实现递归遍历XML的函数，把所有叶子节点的行标识、路径、值收集起来：

CREATE OR REPLACE TYPE xml_flat_node AS OBJECT (
    row_id NUMBER,
    node_path VARCHAR2(1000),
    node_value VARCHAR2(4000)
);
/

CREATE OR REPLACE TYPE xml_flat_node_table AS TABLE OF xml_flat_node;
/

CREATE OR REPLACE FUNCTION parse_xml_to_nodes(p_xml XMLTYPE)
RETURN xml_flat_node_table
IS
    v_nodes xml_flat_node_table := xml_flat_node_table();
    v_row_id NUMBER := 0;
    
    PROCEDURE traverse_nodes(p_node XMLTYPE, p_path VARCHAR2, p_current_row NUMBER)
    IS
        v_child_nodes XMLTYPE;
        v_child_count NUMBER;
        v_child_node XMLTYPE;
        v_node_name VARCHAR2(200);
        v_node_value VARCHAR2(4000);
    BEGIN
        -- 获取当前节点的子节点集合
        v_child_nodes := p_node.extract('/*/*');
        v_child_count := v_child_nodes.existsNode('/*');
        
        IF v_child_count > 0 THEN
            -- 遍历子节点，同名同级节点视为不同行
            FOR i IN 1..v_child_count LOOP
                v_child_node := v_child_nodes.extract('/*['||i||']');
                v_node_name := v_child_node.extract('local-name(/*)').getStringVal();
                
                -- 重复节点生成新行ID
                IF v_child_count > 1 AND i > 1 THEN
                    v_row_id := v_row_id + 1;
                END IF;
                
                traverse_nodes(v_child_node, p_path || '/' || v_node_name, CASE WHEN v_child_count>1 THEN v_row_id ELSE p_current_row END);
            END LOOP;
        ELSE
            -- 叶子节点，存入集合
            v_node_value := p_node.getStringVal();
            v_nodes.extend();
            v_nodes(v_nodes.COUNT) := xml_flat_node(p_current_row, p_path, v_node_value);
        END IF;
    END;
BEGIN
    v_row_id := 1;
    traverse_nodes(p_xml, '/' || p_xml.extract('local-name(/*)').getStringVal(), v_row_id);
    RETURN v_nodes;
END;
/

2. 动态生成扁平临时表的存储过程 #

基于提取的节点数据，动态生成列名，通过PIVOT将行转列，最终创建临时表：

CREATE OR REPLACE PROCEDURE xml_to_flat_table(p_xml XMLTYPE, p_temp_table_name VARCHAR2)
IS
    v_nodes xml_flat_node_table;
    v_col_list VARCHAR2(4000);
    v_pivot_sql VARCHAR2(32767);
BEGIN
    -- 获取节点数据集合
    v_nodes := parse_xml_to_nodes(p_xml);
    
    -- 生成去重后的列名列表
    SELECT LISTAGG(DISTINCT node_path, ' AS "', '" , ') WITHIN GROUP (ORDER BY node_path)
    INTO v_col_list
    FROM TABLE(v_nodes);
    
    -- 构建动态PIVOT SQL
    v_pivot_sql := 'CREATE GLOBAL TEMPORARY TABLE ' || p_temp_table_name || ' ON COMMIT PRESERVE ROWS AS ' ||
                   'SELECT * FROM ' ||
                   '(SELECT row_id, node_path, node_value FROM TABLE(:nodes)) ' ||
                   'PIVOT (MAX(node_value) FOR node_path IN ("' || v_col_list || '"))';
    
    -- 执行动态SQL
    EXECUTE IMMEDIATE v_pivot_sql USING v_nodes;
END;
/

3. 使用示例 #

-- 测试用XML
DECLARE
    v_xml XMLTYPE := XMLTYPE('
        <root>
            <employee>
                <emp_id>1001</emp_id>
                <emp_name>John Doe</emp_name>
                <department>
                    <dept_id>D01</dept_id>
                    <dept_name>Engineering</dept_name>
                </department>
            </employee>
            <employee>
                <emp_id>1002</emp_id>
                <emp_name>Jane Smith</emp_name>
                <department>
                    <dept_id>D02</dept_id>
                    <dept_name>HR</dept_name>
                </department>
            </employee>
        </root>
    ');
BEGIN
    -- 生成临时表EMP_FLAT_DATA
    xml_to_flat_table(v_xml, 'EMP_FLAT_DATA');
END;
/

-- 查询扁平化结果
SELECT * FROM EMP_FLAT_DATA;

关键注意点 #

• 节点路径作为列名时，若包含特殊字符，需额外添加转义逻辑；
• 大型XML解析需考虑性能，可限制节点深度或值的长度；
• 若临时表已存在，需在存储过程中添加DROP TABLE ...的判断逻辑；
• 行的划分规则可通过修改traverse_nodes中的行ID生成逻辑调整，比如自定义行的分组条件。

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