3 Replies Latest reply on Apr 21, 2019 7:09 PM by Subramanyam Yalla-Oracle

    Extract Data Into Multiple Tables From One XML File

    wowl
    wowl Mar 13, 2019 7:30 PM

      Hi,

       

      I'm sure this has been asked before and apologise if it has but i couldnt find anything.

       

      I have an xml file that contains data for multiple tables and I need to extract it.  I've loaded the table into a clob and plan to use code below but i'm having trouble with the structure of the XML

       

      I've put the xml below so if anyone could tell how how to extract the data i would appreaciate it.

       

      declare

        v_clob         clob;

        v_xml          XMLType;

      BEGIN

       

      select clob_col into v_clob from temp_tab;

       

      v_xml          := XMLTYPE.createXML(v_clob);

       

        FOR r IN

        (SELECT   ExtractValue(Value(p),'/Table/Record/Field()') AS Product_Group

        FROM TABLE(XMLSequence(Extract(v_xml,'/XmlInterfaceRequest/AddPolicyTableData/PolicyTableData/Table'))) p

        )

        LOOP

          insert into temp_tab2 (col_a) values (r.Product_Group);

        END LOOP;

      commit;

      end;

       

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

      <XmlInterfaceRequest>

          <AddPolicyTableData>

              <PolicyTableData>

                  <Name>Business_Domestic_P_Enforcement_V1.0</Name>

                  <Description></Description>

                  <Table>

                      <Header>

                          <Field MatchingOperationAlgorithm="Equivalence" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="csv" Key="true" Field="Product_Group">User.Custom14</Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="PRule" Key="false" Field="Quota_P_Rules"></Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="PRule" Key="false" Field="Out_of_quota_P_Rules"></Field>

                      </Header>

                      <Record Name="1528376888590">

                          <Field Field="Product_Group">D101</Field>

                          <Field Field="Quota_P_Rules">DataAccessGroup</Field>

                          <Field Field="Out_of_quota_P_Rules">MBBRunOutOfDataGroup</Field>

                      </Record>

                      <Record Name="1529500786789">

                          <Field Field="Product_Group">D102</Field>

                          <Field Field="Quota_P_Rules">DataAccessGroup</Field>

                          <Field Field="Out_of_quota_P_Rules">RunOutOfDataGroup</Field>

                      </Record>

                      <Record Name="1529500793822">

                          <Field Field="Product_Group">D103</Field>

                          <Field Field="Quota_P_Rules">DataAccessGroup</Field>

                          <Field Field="Out_of_quota_P_Rules">DataAccessGroup</Field>

                      </Record>

                      <Record Name="1547541900838">

                          <Field Field="Product_Group">D130</Field>

                          <Field Field="Quota_P_Rules">DataAccessGroup</Field>

                          <Field Field="Out_of_quota_P_Rules">ZeroBandWidth</Field>

                      </Record>

                  </Table>

              </PolicyTableData>

              <PolicyTableData>

                  <Name>Business.Monitored_Product_Groups</Name>

                  <Description></Description>

                  <Table>

                      <Header>

                          <Field MatchingOperationAlgorithm="Equivalence" MatchingDelimiterValue="," MatchingDelimiterKey="true" Type="csv" Key="true" Field="Domestic_Groups">User.Custom14</Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="csv" Key="false" Field="Product_Group"></Field>

                      </Header>

                      <Record Name="1529404052034">

                          <Field Field="Domestic_Groups">D101</Field>

                          <Field Field="Product_Group">D101</Field>

                      </Record>

                      <Record Name="1547543310497">

                          <Field Field="Domestic_Groups">D103</Field>

                          <Field Field="Product_Group">D103</Field>

                      </Record>

                  </Table>

              </PolicyTableData>

              <PolicyTableData>

                  <Name>Business.X_S_V1</Name>

                  <Description></Description>

                  <Table>

                      <Header>

                          <Field MatchingOperationAlgorithm="Wildcard" MatchingDelimiterValue="," MatchingDelimiterKey="true" Type="csv" Key="true" Field="X_Quota">User.Custom7</Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="notificationContent" Key="false" Field="S0_ID"></Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="notificationContent" Key="false" Field="S_BASE_0"></Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="notificationContent" Key="false" Field="S80_ID"></Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="notificationContent" Key="false" Field="S_BASE_80"></Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="notificationContent" Key="false" Field="S100_ID"></Field>

                          <Field MatchingOperationAlgorithm="" MatchingDelimiterValue="" MatchingDelimiterKey="false" Type="notificationContent" Key="false" Field="S_BASE_100"></Field>

                      </Header>

                      <Record Name="1529415278706">

                          <Field Field="X_Quota">X_220</Field>

                          <Field Field="S0_ID">N/A</Field>

                          <Field Field="S_BASE_0">N/A</Field>

                          <Field Field="S80_ID">65</Field>

                          <Field Field="S_BASE_80">Notes</Field>

                          <Field Field="S100_ID">69</Field>

                          <Field Field="S_BASE_100">Notes</Field>

                      </Record>

                      <Record Name="1548058239095">

                          <Field Field="X_Quota">X_630</Field>

                          <Field Field="S0_ID">N/A</Field>

                          <Field Field="S_BASE_0">N/A</Field>

                          <Field Field="S80_ID">21</Field>

                          <Field Field="S_BASE_80">Notes</Field>

                          <Field Field="S100_ID">23</Field>

                          <Field Field="S_BASE_100">Notes</Field>

                      </Record>

                  </Table>

              </PolicyTableData>

          </AddPolicyTableData>

      </XmlInterfaceRequest>

      • 732 Views
      • Tags:
        • 1. Re: Extract Data Into Multiple Tables From One XML File
          cormaco
          cormaco Mar 21, 2019 5:00 PM (in response to wowl)

          Don't use extract or extractvalue, these functions are deprecated. Use xmltable and xmlquery instead:

             SQL/XML Functions XMLQUERY, XMLTABLE, XMLExists, and XMLCast 

           

          I think you want something like this:

          select PolicyTableData,ProductGroup
from 
    temp_tab,
    xmltable(
        '/XmlInterfaceRequest/AddPolicyTableData/PolicyTableData'
        passing xmltype(clob_col)
        columns
            PolicyTableData varchar2(50) path 'Name',
            Record xmltype path 'Table/Record'
    ),
    xmltable(
        'Record'
        passing Record
        columns
            RecordName varchar2(13) path '@Name',
            ProductGroup varchar2(10) path 'Field[@Field="Product_Group"]'
    )
            

POLICYTABLEDATA                                    PRODUCTGRO
-------------------------------------------------- ----------
Business_Domestic_P_Enforcement_V1.0               D101      
Business_Domestic_P_Enforcement_V1.0               D102      
Business_Domestic_P_Enforcement_V1.0               D103      
Business_Domestic_P_Enforcement_V1.0               D130      
Business.Monitored_Product_Groups                  D101      
Business.Monitored_Product_Groups                  D103      
Business.X_S_V1                                              
Business.X_S_V1                                              

8 rows selected.
          • 2. Re: Extract Data Into Multiple Tables From One XML File
            wowl
            wowl Mar 22, 2019 10:22 PM (in response to cormaco)

            Thanks for your help, that is exactly what i needed and i can now see what i'm doing.

            • 3. Re: Extract Data Into Multiple Tables From One XML File
              Subramanyam Yalla-Oracle
              Subramanyam Yalla-Oracle Apr 21, 2019 7:09 PM (in response to wowl)

              Hi,

               

              I have similar requirement. I will browsing xml data file from OAF page and loading the xml data file to oracle apps database in fnd_documents table. Now I need to read the stored xml data file from database and insert into tasks database table.

               

              Please advise how to handle this scenario.

               

              Thanks,

              Subramanyam.