You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

XML解析并写入Oracle数据库表技术求助

avatar
云上老周
2026-5-14
解析SOAP XML并将Resultset数据写入Oracle表

看起来你已经迈出了XML解析的第一步，但目前代码只处理了SOAP响应里的全局字段，还没触达你真正需要的ChildFolders结果集数据。我来帮你完善代码，实现把每行字段值对应插入Oracle表的功能。

第一步：准备目标Oracle表

首先得确保Oracle里有和XML字段匹配的表，列名要对应XML中field的name属性（Oracle默认存储列名为大写，下面示例用大写列名），你可以用这个建表语句：

CREATE TABLE CHILD_FOLDERS (
    FFOLDERGUID VARCHAR2(100),
    FPARENTGUID VARCHAR2(100),
    FFOLDERNAME VARCHAR2(200),
    FFOLDERTYPE VARCHAR2(50),
    FINHIBITPROPAGATION NUMBER(1),
    FPROMPTFORMETADATA NUMBER(1),
    FISCONTRIBUTION NUMBER(1),
    FISINTRASH NUMBER(1),
    FREALITEMGUID VARCHAR2(100),
    FLIBRARYTYPE NUMBER(1),
    FISLIBRARY NUMBER(1),
    FDOCCLASSES VARCHAR2(200),
    FTARGETGUID VARCHAR2(100),
    FAPPLICATION VARCHAR2(100),
    FOWNER VARCHAR2(100),
    FCREATOR VARCHAR2(100),
    FLASTMODIFIER VARCHAR2(100),
    FCREATEDATE VARCHAR2(50), -- 若需日期类型，后续可通过TO_DATE转换
    FLASTMODIFIEDDATE VARCHAR2(50),
    FSECURITYGROUP VARCHAR2(100),
    FDOCACCOUNT VARCHAR2(200),
    FCLBRAUSERLIST VARCHAR2(200),
    FCLBRAALIASLIST VARCHAR2(200),
    FCLBRAROLELIST VARCHAR2(200),
    FFOLDERDESCRIPTION VARCHAR2(4000),
    FCHILDFOLDERSCOUNT NUMBER(5),
    FCHILDFILESCOUNT NUMBER(5),
    FISSUBSCRIBED NUMBER(1),
    FDISPLAYNAME VARCHAR2(200),
    FDISPLAYDESCRIPTION VARCHAR2(4000)
);

第二步：修改PL/SQL代码解析结果集并插入数据

我提供两种方案，优先推荐第一种，可读性和性能更稳定；第二种适合字段可能动态变化的场景。

方案一：显式字段解析（推荐）

这种方式直接指定每个字段的提取路径，适合字段固定的场景：

DECLARE
    l_xml XMLTYPE;
    v_xml CLOB;
BEGIN
    -- 从你的test_xml表获取XML内容
    SELECT t_xml INTO v_xml FROM test_xml;
    l_xml := XMLTYPE.createXML(v_xml);

    -- 批量插入ChildFolders下的所有行
    INSERT INTO CHILD_FOLDERS (
        FFOLDERGUID, FPARENTGUID, FFOLDERNAME, FFOLDERTYPE,
        FINHIBITPROPAGATION, FPROMPTFORMETADATA, FISCONTRIBUTION,
        FISINTRASH, FREALITEMGUID, FLIBRARYTYPE, FISLIBRARY,
        FDOCCLASSES, FTARGETGUID, FAPPLICATION, FOWNER,
        FCREATOR, FLASTMODIFIER, FCREATEDATE, FLASTMODIFIEDDATE,
        FSECURITYGROUP, FDOCACCOUNT, FCLBRAUSERLIST, FCLBRAALIASLIST,
        FCLBRAROLELIST, FFOLDERDESCRIPTION, FCHILDFOLDERSCOUNT,
        FCHILDFILESCOUNT, FISSUBSCRIBED, FDISPLAYNAME, FDISPLAYDESCRIPTION
    )
    SELECT
        -- 逐个提取每个field的值，注意数据类型转换
        XMLQUERY('/idc:row/idc:field[@name="fFolderGUID"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fParentGUID"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fFolderName"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fFolderType"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fInhibitPropagation"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fPromptForMetadata"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fIsContribution"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fIsInTrash"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        XMLQUERY('/idc:row/idc:field[@name="fRealItemGUID"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fLibraryType"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fIsLibrary"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        XMLQUERY('/idc:row/idc:field[@name="fDocClasses"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fTargetGUID"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fApplication"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fOwner"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fCreator"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fLastModifier"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fCreateDate"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fLastModifiedDate"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fSecurityGroup"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fDocAccount"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fClbraUserList"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fClbraAliasList"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fClbraRoleList"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fFolderDescription"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fChildFoldersCount"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fChildFilesCount"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        TO_NUMBER(XMLQUERY('/idc:row/idc:field[@name="fIsSubscribed"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()),
        XMLQUERY('/idc:row/idc:field[@name="fDisplayName"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal(),
        XMLQUERY('/idc:row/idc:field[@name="fDisplayDescription"]/text()' PASSING row_xml RETURNING CONTENT).getStringVal()
    FROM
        dual,
        XMLTABLE(
            XMLNAMESPACES(
                'http://schemas.xmlsoap.org/soap/envelope/' AS "SOAP-ENV",
                'http://www.stellent.com/IdcService/' AS "idc"
            ),
            '/SOAP-ENV:Envelope/SOAP-ENV:Body/idc:service/idc:document/idc:resultset[@name="ChildFolders"]/idc:row'
            PASSING l_xml
            COLUMNS row_xml XMLTYPE PATH '.'
        );

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('搞定！一共插入了' || SQL%ROWCOUNT || '行数据');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('插入出错了：' || SQLERRM);
        RAISE; -- 可选择抛出异常或自行处理
END;
/

方案二：动态解析（适合字段不固定的场景）

如果XML字段可能随时变化，不想每次修改代码，可以用动态SQL自动识别字段：

DECLARE
    l_xml XMLTYPE;
    v_xml CLOB;
    v_columns VARCHAR2(4000);
    v_values VARCHAR2(4000);
    v_sql VARCHAR2(4000);
BEGIN
    SELECT t_xml INTO v_xml FROM test_xml;
    l_xml := XMLTYPE.createXML(v_xml);

    -- 从第一行获取所有字段名，构建插入列名
    SELECT LISTAGG('"' || t."key" || '"', ',') WITHIN GROUP (ORDER BY t."key") INTO v_columns
    FROM dual,
         XMLTABLE(
             XMLNAMESPACES('http://www.stellent.com/IdcService/' AS "idc"),
             '/idc:row/idc:field'
             PASSING (SELECT XMLQUERY('/SOAP-ENV:Envelope/SOAP-ENV:Body/idc:service/idc:document/idc:resultset[@name="ChildFolders"]/idc:row[1]' PASSING l_xml RETURNING CONTENT) FROM dual)
             COLUMNS "key" VARCHAR2(100) PATH '@name'
         ) t;

    -- 构建每个字段的值提取语句
    SELECT LISTAGG('XMLQUERY(''/idc:row/idc:field[@name="''' || t."key" || '''"]/text()'' PASSING row_xml RETURNING CONTENT).getStringVal()', ',') WITHIN GROUP (ORDER BY t."key") INTO v_values
    FROM dual,
         XMLTABLE(
             XMLNAMESPACES('http://www.stellent.com/IdcService/' AS "idc"),
             '/idc:row/idc:field'
             PASSING (SELECT XMLQUERY('/SOAP-ENV:Envelope/SOAP-ENV:Body/idc:service/idc:document/idc:resultset[@name="ChildFolders"]/idc:row[1]' PASSING l_xml RETURNING CONTENT) FROM dual)
             COLUMNS "key" VARCHAR2(100) PATH '@name'
         ) t;

    -- 组装插入SQL
    v_sql := 'INSERT INTO CHILD_FOLDERS (' || v_columns || ') SELECT ' || v_values || ' FROM dual, XMLTABLE(XMLNAMESPACES(''http://schemas.xmlsoap.org/soap/envelope/'' AS "SOAP-ENV", ''http://www.stellent.com/IdcService/'' AS "idc"), ''/SOAP-ENV:Envelope/SOAP-ENV:Body/idc:service/idc:document/idc:resultset[@name="ChildFolders"]/idc:row'' PASSING :l_xml COLUMNS row_xml XMLTYPE PATH '''' )';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql USING l_xml;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('动态插入完成，共插入' || SQL%ROWCOUNT || '行');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('动态插入失败：' || SQLERRM);
        RAISE;
END;
/

关键注意事项

  • 命名空间不能遗漏：XMLTABLE必须正确指定SOAP-ENV和idc命名空间，否则会无法解析节点。
  • 数据类型要匹配：数字类型字段需用TO_NUMBER转换字符串值；日期类型可通过TO_DATE(日期字符串, 'MM/DD/RR HH:MI AM')转为Oracle DATE类型。
  • 空值处理：XML中空字段会提取为空字符串，Oracle会自动转为NULL（需确保表列允许NULL）。
  • 性能优化：若结果集最多100行，两种方案都可行，但显式解析的性能更优。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.05.14 08:45:50