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

      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>

        • 1. Re: Extract Data Into Multiple Tables From One XML File
          cormaco

          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

            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

              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.