CODEWITHSUNDEEP
sqlxmloracle-databasexmltable
oracle_of
oracle_of

Reputation: 23

Need help parsing XML and storing in Oracle table

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;

Upvotes: 2

Views: 94

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21063

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.

Upvotes: 2

Related Questions