Need help parsing XML and storing in Oracle table

Question

I am trying to parse this sample output and storing the rows in this xml as rows in table, column names will the name="...." in the rows.

There will be upto 100 rows in resultset, I'm including just one below as example.

<?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>

I need to populate table whose column names match field name='...' with corresponding values. For example one of the key above <idc:field name="fFolderType">owner</idc:field> will populate column name "fFolderType" with value "owner"

What I have done so far...

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;

What did you tried thus far? Additionally it would be enough if you show a XML with say three fields (i.e. *minimal example*) — Marmite Bomber, Nov 09 '19 at 12:57
Use `edit` to add the code in your *question* (not in an aswer) — Marmite Bomber, Nov 09 '19 at 15:51
Sorry about that, I have added my code to the question. Thanks. — oracle_of, Nov 09 '19 at 16:19

score 2 · Accepted Answer · answered Nov 09 '19 at 16:48

Well this is a static (trival) solution, provided the column are known and fixed:

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
;

Note that I simplified your data removing not relevant parts and considered only sample columns.

You'd have to change for dynamic SQL to be flexible on the columns names in the XML.

Need help parsing XML and storing in Oracle table

1 Answers1