如何编写存储过程将本地XML文件导入Oracle已有表xml_final_table

需求回顾 #

• 接收本地文件路径作为参数，读取路径下所有XML文件
• 提取XML中<Line>节点的所有字段，直接插入已存在的xml_final_table
• 无需使用中间表

解决方案 #

前提准备 #

1. 确保数据库用户拥有UTL_FILE权限，执行以下授权语句（需DBA权限）：

GRANT EXECUTE ON UTL_FILE TO your_username;

2. 确认xml_final_table的字段与<Line>节点的字段匹配，示例表结构参考（根据实际调整）：

CREATE TABLE xml_final_table (
    line_number NUMBER,
    product_code VARCHAR2(100),
    product_description VARCHAR2(200),
    quantity NUMBER,
    unit_of_measure VARCHAR2(10),
    unit_price NUMBER(10,4),
    tax_point_date DATE,
    description VARCHAR2(100),
    credit_amount NUMBER(10,4),
    tax_type VARCHAR2(20),
    tax_country_region VARCHAR2(10),
    tax_code VARCHAR2(20),
    tax_percentage NUMBER(5,2),
    settlement_amount NUMBER(10,4)
);

存储过程代码 #

CREATE OR REPLACE PROCEDURE import_xml_lines(p_dir_path IN VARCHAR2) IS
    v_file_handle UTL_FILE.FILE_TYPE;
    v_file_name VARCHAR2(255);
    v_xml_content CLOB;
    v_buffer VARCHAR2(32767);
    v_xml XMLType;
    -- 定义目录对象（需手动创建，对应本地路径）
    v_dir VARCHAR2(100) := 'XML_IMPORT_DIR';
BEGIN
    -- 创建目录对象（仅首次执行，需DBA权限）
    -- EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' || v_dir || ' AS ''' || p_dir_path || '''';

    -- 遍历目录下的XML文件
    v_file_name := UTL_FILE.GET_FILENAME(v_dir, '*.xml');
    WHILE v_file_name IS NOT NULL LOOP
        -- 初始化CLOB存储XML内容
        DBMS_LOB.CREATETEMPORARY(v_xml_content, TRUE);
        
        -- 读取XML文件内容到CLOB
        v_file_handle := UTL_FILE.FOPEN(v_dir, v_file_name, 'R', 32767);
        LOOP
            BEGIN
                UTL_FILE.GET_LINE(v_file_handle, v_buffer);
                DBMS_LOB.WRITEAPPEND(v_xml_content, LENGTH(v_buffer), v_buffer);
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    EXIT;
            END;
        END LOOP;
        UTL_FILE.FCLOSE(v_file_handle);

        -- 将CLOB转换为XMLType（处理无根节点的XML，包裹临时根标签）
        v_xml := XMLType('<root>' || v_xml_content || '</root>');
        
        -- 提取Line节点数据并插入目标表
        INSERT INTO xml_final_table (
            line_number,
            product_code,
            product_description,
            quantity,
            unit_of_measure,
            unit_price,
            tax_point_date,
            description,
            credit_amount,
            tax_type,
            tax_country_region,
            tax_code,
            tax_percentage,
            settlement_amount
        )
        SELECT
            x.line_number,
            x.product_code,
            x.product_description,
            x.quantity,
            x.unit_of_measure,
            x.unit_price,
            TO_DATE(x.tax_point_date, 'YYYY-MM-DD'),
            x.description,
            x.credit_amount,
            x.tax_type,
            x.tax_country_region,
            x.tax_code,
            x.tax_percentage,
            x.settlement_amount
        FROM XMLTable(
            '/root/Invoice/Line'
            PASSING v_xml
            COLUMNS
                line_number NUMBER PATH 'LineNumber',
                product_code VARCHAR2(100) PATH 'ProductCode',
                product_description VARCHAR2(200) PATH 'ProductDescription',
                quantity NUMBER PATH 'Quantity',
                unit_of_measure VARCHAR2(10) PATH 'UnitOfMeasure',
                unit_price NUMBER(10,4) PATH 'UnitPrice',
                tax_point_date VARCHAR2(10) PATH 'TaxPointDate',
                description VARCHAR2(100) PATH 'Description',
                credit_amount NUMBER(10,4) PATH 'CreditAmount',
                tax_type VARCHAR2(20) PATH 'Tax/TaxType',
                tax_country_region VARCHAR2(10) PATH 'Tax/TaxCountryRegion',
                tax_code VARCHAR2(20) PATH 'Tax/TaxCode',
                tax_percentage NUMBER(5,2) PATH 'Tax/TaxPercentage',
                settlement_amount NUMBER(10,4) PATH 'SettlementAmount'
        ) x;
        
        COMMIT;
        
        -- 释放临时CLOB
        DBMS_LOB.FREETEMPORARY(v_xml_content);
        
        -- 获取下一个XML文件
        v_file_name := UTL_FILE.GET_FILENAME(v_dir, '*.xml', v_file_name);
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        IF UTL_FILE.IS_OPEN(v_file_handle) THEN
            UTL_FILE.FCLOSE(v_file_handle);
        END IF;
        IF DBMS_LOB.ISTEMPORARY(v_xml_content) = 1 THEN
            DBMS_LOB.FREETEMPORARY(v_xml_content);
        END IF;
        RAISE;
END;
/

关键说明 #

1. 目录对象处理：Oracle通过目录对象映射本地路径，代码中注释的创建目录语句需由DBA执行，或者提前手动创建。
2. XML无根节点处理：用户提供的XML没有统一根节点，通过包裹<root>标签让XMLType可以正常解析。
3. XMLTable的使用：替代老旧的ExtractValue和XMLSequence，更高效且符合Oracle新版本的语法规范，直接映射XML节点到SQL列。
4. 异常处理：确保文件和临时CLOB在异常时正确关闭/释放，避免资源泄漏。

调用方式 #

BEGIN
    import_xml_lines('C:\tmp'); -- 替换为实际本地路径
END;
/

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