如何将SOAP格式XML文件加载至Oracle数据库表？

针对你提供的SOAP XML结构，下面是几种高效的导入方案，你可以根据数据量和需求选择：

第一步：创建匹配的数据库表 #

先建一张和XML字段对应的表，字段类型可按需调整：

CREATE TABLE EMPLOYEE_DATA (
    EMPLOYEE_CODE VARCHAR2(50),
    GROUP_NAME VARCHAR2(50),
    EMPLOYEE_NAME VARCHAR2(100),
    EMP_MAIL_ID VARCHAR2(100),
    ID NUMBER,
    "DATE" TIMESTAMP
);

方案1：SQL/XML直接解析插入（适合单条/少量数据） #

如果只是处理单条或少量XML，直接用SQL结合XMLType解析插入最快捷。下面的语句会直接解析SOAP结构，提取字段插入表中：

INSERT INTO EMPLOYEE_DATA (EMPLOYEE_CODE, GROUP_NAME, EMPLOYEE_NAME, EMP_MAIL_ID, ID, "DATE")
SELECT
    emp_code,
    grp_name,
    emp_name,
    emp_mail,
    emp_id,
    to_timestamp(emp_date, 'YYYY-MM-DD"T"HH24:MI:SS.FF3') AS emp_timestamp
FROM
    XMLTable(
        XMLNamespaces(
            'http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
            'http://www.example.com/' AS "ns"
        ),
        '/soapenv:Envelope/soapenv:Body/ns:add'
        PASSING XMLType('
<soapenv:Envelope
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:ns="http://www.example.com/"><soapenv:Header/></soapenv:Body><ns:add><Employee_Code>user</Employee_Code><Group_Name>Group</Group_Name>
<Employee_Name>user</Employee_Name><Emp_Mail_ID>abc@gmail.com</Emp_Mail_ID><ID>1</ID><Date>2023-02-17T11:40:26.145</Date></ns:add></soapenv:Body></soapenv:Envelope>
        ')
        COLUMNS
            emp_code VARCHAR2(50) PATH 'Employee_Code',
            grp_name VARCHAR2(50) PATH 'Group_Name',
            emp_name VARCHAR2(100) PATH 'Employee_Name',
            emp_mail VARCHAR2(100) PATH 'Emp_Mail_ID',
            emp_id NUMBER PATH 'ID',
            emp_date VARCHAR2(30) PATH 'Date'
    );

如果XML存放在服务器文件里，先创建目录对象并授权，再用BFILENAME读取：

-- 创建目录（替换为你的XML文件实际路径）
CREATE OR REPLACE DIRECTORY XML_FILE_DIR AS '/opt/oracle/xml_files';
-- 给操作用户授权
GRANT READ ON DIRECTORY XML_FILE_DIR TO YOUR_DB_USER;

然后修改上面SQL中PASSING部分：

PASSING XMLType(BFILENAME('XML_FILE_DIR', 'your_soap_file.xml'), NLS_CHARSET_ID('AL32UTF8'))

方案2：SQL*Loader批量导入（适合大量XML文件） #

如果要批量处理多个SOAP XML文件，SQL*Loader是性能最优的选择。步骤如下：

1. 编写控制文件soap_import.ctl：

LOAD DATA
INFILE '/path/to/your/xml_files/*.xml' -- 批量读取目录下所有XML文件
APPEND INTO TABLE EMPLOYEE_DATA
XMLTYPE(XML_CONTENT)
PRESERVE BLANKS
(
    XML_CONTENT CHAR(4000), -- 临时存储整个XML内容
    EMPLOYEE_CODE EXPRESSION "XMLTYPE(:XML_CONTENT).extract('/soapenv:Envelope/soapenv:Body/ns:add/Employee_Code/text()', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"').getStringVal()",
    GROUP_NAME EXPRESSION "XMLTYPE(:XML_CONTENT).extract('/soapenv:Envelope/soapenv:Body/ns:add/Group_Name/text()', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"').getStringVal()",
    EMPLOYEE_NAME EXPRESSION "XMLTYPE(:XML_CONTENT).extract('/soapenv:Envelope/soapenv:Body/ns:add/Employee_Name/text()', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"').getStringVal()",
    EMP_MAIL_ID EXPRESSION "XMLTYPE(:XML_CONTENT).extract('/soapenv:Envelope/soapenv:Body/ns:add/Emp_Mail_ID/text()', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"').getStringVal()",
    ID EXPRESSION "TO_NUMBER(XMLTYPE(:XML_CONTENT).extract('/soapenv:Envelope/soapenv:Body/ns:add/ID/text()', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"').getStringVal())",
    "DATE" EXPRESSION "TO_TIMESTAMP(XMLTYPE(:XML_CONTENT).extract('/soapenv:Envelope/soapenv:Body/ns:add/Date/text()', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"').getStringVal(), 'YYYY-MM-DD"T"HH24:MI:SS.FF3')"
)

2. 执行SQL*Loader命令（替换为你的数据库账号信息）：

sqlldr userid=db_username/db_password@db_service control=soap_import.ctl log=soap_import.log

方案3：PL/SQL程序导入（适合复杂逻辑/自动化） #

如果需要处理复杂的XML校验、错误处理，或者要定时自动导入，写PL/SQL程序更灵活：

DECLARE
    v_xml XMLType;
    v_dir VARCHAR2(100) := 'XML_FILE_DIR'; -- 之前创建的目录对象名
    v_filename VARCHAR2(100) := 'soap_data.xml';
BEGIN
    -- 读取服务器上的XML文件
    v_xml := XMLType(BFILENAME(v_dir, v_filename), NLS_CHARSET_ID('AL32UTF8'));
    
    -- 解析XML并插入数据
    INSERT INTO EMPLOYEE_DATA
    SELECT
        EXTRACTVALUE(v_xml, '/soapenv:Envelope/soapenv:Body/ns:add/Employee_Code', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"'),
        EXTRACTVALUE(v_xml, '/soapenv:Envelope/soapenv:Body/ns:add/Group_Name', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"'),
        EXTRACTVALUE(v_xml, '/soapenv:Envelope/soapenv:Body/ns:add/Employee_Name', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"'),
        EXTRACTVALUE(v_xml, '/soapenv:Envelope/soapenv:Body/ns:add/Emp_Mail_ID', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"'),
        TO_NUMBER(EXTRACTVALUE(v_xml, '/soapenv:Envelope/soapenv:Body/ns:add/ID', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"')),
        TO_TIMESTAMP(EXTRACTVALUE(v_xml, '/soapenv:Envelope/soapenv:Body/ns:add/Date', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.example.com/"'), 'YYYY-MM-DD"T"HH24:MI:SS.FF3')
    FROM DUAL;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('导入出错：' || SQLERRM);
END;
/

方案选择建议 #

• 单条/少量XML：用方案1，操作简单，无需额外工具
• 批量XML文件：用方案2，性能最高，适合大规模导入
• 复杂逻辑/自动化：用方案3，可自定义错误处理、校验规则

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