sql - 需要帮助解析 XML 并存储在 Oracle 表中

Question

我正在尝试解析此示例输出并将此 xml 中的行存储为表中的行，列名将 name="...."在行中。

结果集中将有多达 100 行，我在下面仅包括一行作为示例。

<?xml version='1.0' encoding='utf-8' ?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Body>
<idc:service xmlns:idc="http://www.stellent.com/IdcService/" IdcService="FLD_BROWSE">
<idc:document dUser="wcpadmin">
<idc:field name="hasMoreResults">0</idc:field>
<idc:field name="localizedForResponse">1</idc:field>
<idc:field name="processPathForRelativeRoot">&#x2F;Enterprise Libraries</idc:field>
<idc:field name="numFiles">0</idc:field>
<idc:field name="itemCount">50</idc:field>
<idc:field name="folderPathLocalized">&#x2F;Enterprise Libraries</idc:field>
<idc:field name="TotalChildFilesCount">0</idc:field>
<idc:field name="isBrowse">1</idc:field>
<idc:field name="doMarkSubscribed">1</idc:field>
<idc:field name="itemStartRow">0</idc:field>
<idc:field name="itemSortField">fFileName</idc:field>
<idc:field name="idcToken">1573401382230:7C68CA0D0B4BE93F5085447A64366E60</idc:field>
<idc:resultset name="ChildFolders" TotalRows="50">
<idc:row>
<idc:field name="fFolderGUID">D85C85A51CB951F0C2503472A5E70C59</idc:field>
<idc:field name="fParentGUID">FLD_ENTERPRISE_LIBRARY</idc:field>
<idc:field name="fFolderName">1405A17F862FDE0DE050849C27347A0B</idc:field>
<idc:field name="fFolderType">owner</idc:field>
<idc:field name="fInhibitPropagation">0</idc:field>
<idc:field name="fPromptForMetadata">0</idc:field>
<idc:field name="fIsContribution">1</idc:field>
<idc:field name="fIsInTrash">0</idc:field>
<idc:field name="fRealItemGUID"></idc:field>
<idc:field name="fLibraryType">1</idc:field>
<idc:field name="fIsLibrary">1</idc:field>
<idc:field name="fDocClasses"></idc:field>
<idc:field name="fTargetGUID"></idc:field>
<idc:field name="fApplication">framework</idc:field>
<idc:field name="fOwner">sysadmin</idc:field>
<idc:field name="fCreator">sysadmin</idc:field>
<idc:field name="fLastModifier">sysadmin</idc:field>
<idc:field name="fCreateDate">1&#x2F;10&#x2F;17 11:37 AM</idc:field>
<idc:field name="fLastModifiedDate">1&#x2F;10&#x2F;17 11:37 AM</idc:field>
<idc:field name="fSecurityGroup">PersonalSpaces</idc:field>
<idc:field name="fDocAccount">PEWebCenter&#x2F;934b7fe3-8645-4e0d-abd8-9c8e7aa819a8</idc:field>
<idc:field name="fClbraUserList"></idc:field>
<idc:field name="fClbraAliasList"></idc:field>
<idc:field name="fClbraRoleList"></idc:field>
<idc:field name="fFolderDescription"></idc:field>
<idc:field name="fChildFoldersCount">1</idc:field>
<idc:field name="fChildFilesCount">0</idc:field>
  <idc:field name="fIsSubscribed">0</idc:field>
<idc:field name="fDisplayName">1405A17F862FDE0DE050849C27347A0B</idc:field>
<idc:field name="fDisplayDescription"></idc:field>
</idc:row>

我需要填充列名匹配字段名称 ='...' 的表
与相应的值。例如上面的键之一 <idc:field name="fFolderType">owner</idc:field>将使用值“owner”填充列名称“fFolderType”

到目前为止我所做的...

declare
  l_xml  xmltype;
  v_xml CLOB;
begin  
select t_xml into v_xml from test_xml;
  l_xml := xmltype.createXML(v_xml);
  for i in (SELECT t."key", t."value"
            FROM dual d,
            XMLTABLE(xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as "xsi",
                                   'http://schemas.xmlsoap.org/soap/envelope/' as "SOAP-ENV",
                                       'http://www.stellent.com/IdcService/' as "idc",
                                   'http://schemas.xmlsoap.org/soap/encoding/' as "SOAP-ENC"), 
                    '/SOAP-ENV:Envelope/SOAP-ENV:Body/idc:service/idc:document/idc:field'
              PASSING l_xml
              COLUMNS 
                 "value"   varchar2(4000) PATH '/',
                 "key" varchar2(4000) PATH '@name') t)
  loop
    dbms_output.put_line('Key is: '||i."key");   
    dbms_output.put_line('Value is: '||i."value");
  end loop;
end;

Answer 1

好吧，这是一个静态(琐碎)解决方案，前提是该列是已知且固定的:

with doc as (
select 
xmltype(q'{<?xml version='1.0' encoding='utf-8' ?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/">
    <SOAP-ENV:Body>
        <idc:service xmlns:idc="http://www.stellent.com/IdcService/" IdcService="FLD_BROWSE">
            <idc:document dUser="wcpadmin">
                <idc:resultset name="ChildFolders" TotalRows="2">
                    <idc:row>
                        <idc:field name="fFolderGUID">D85C85A51CB951F0C2503472A5E70C59</idc:field>
                        <idc:field name="fParentGUID">FLD_ENTERPRISE_LIBRARY</idc:field>
                        <idc:field name="fFolderName">1405A17F862FDE0DE050849C27347A0B</idc:field>
                        <idc:field name="fFolderType">owner</idc:field>
                    </idc:row>
                    <idc:row>
                        <idc:field name="fFolderGUID">D85C85A51CB951F0C2503472A5E70C52</idc:field>
                        <idc:field name="fParentGUID">FLD_ENTERPRISE_LIBRARY2</idc:field>
                        <idc:field name="fFolderName">1405A17F862FDE0DE050849C27347A02</idc:field>
                        <idc:field name="fFolderType">owner2</idc:field>
                    </idc:row>
                </idc:resultset>
            </idc:document>
        </idc:service>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>}') as doc from  DUAL)
select x.* from doc,
         XMLTable(
            XMLNAMESPACES (DEFAULT  'http://www.stellent.com/IdcService/'
               ),
          'for $i in //resultset/row    
           return $i'
          passing  (doc.doc)
          columns
                 fFolderGUID varchar2(4000) path '//field[@name="fFolderGUID"]',
                 fParentGUID varchar2(4000) path '//field[@name="fParentGUID"]',
                 fFolderName varchar2(4000) path '//field[@name="fFolderName"]',
                 fFolderType varchar2(4000) path '//field[@name="fFolderType"]' 
                  ) x
;

请注意，我简化了您的数据，删除了不相关的部分并仅考虑了示例列。

您必须更改动态 SQL 才能灵活处理 XML 中的列名称。