Load XML data into Oracle table

Steps to Load XML File to Oracle Tables

Solution : Sample XML file has been used for illustration

Step 1 : Create a directory using the below command. The below directory will be created in /tmp folder. We need to mention the appropriate path as where the directory to be created.

     CREATE DIRECTORY  < DIRECTORY_NAME> AS '/tmp';

Step 2 : Create a table with XMLType as Datatype - This table will be used to store the XML Data from the directory created above.

       CREATE TABLE table_with_xml_column  (    file_id  NUMBER,   

       filename VARCHAR2(64),    xml_document XMLType  );

Step 3: Create another table to store the values of xml

      create table temp_xml(file_content XMLType)

Step 4:  Insert data to the XMLType table table_with_xml_column

        INSERT INTO  table_with_xml_column (xml_document)  VALUES

        (XMLType(bfilename('XMLDIR', 'mlc_gl_daily_rates.xml'),     

        nls_charset_id('AL32UTF8')));

        Note : XMLDIR is the directory we created.

Step 5 : The below Query will insert data to the Temporary table by reading the values  

       from TEMP_XML table.

set serveroutput on;

DECLARE

v_xml xmltype :=xmltype('<WebServiceResponse xmlns="http://ws.abc.com">

    <SubscriptionInfo xmlns="http://ws.oracle.com">

      <LicenseStatusCode>0</LicenseStatusCode>

      <LicenseStatus>123</LicenseStatus>

      <LicenseActionCode>1</LicenseActionCode>

      <LicenseAction>Decremented hit count</LicenseAction>

      <RemainingHits>5993</RemainingHits>

      <Amount>0</Amount>

    </SubscriptionInfo>

    <GetResponse xmlns="http://www.abc.com">

      <GetData>

        <ServiceStatus>

          <StatusNbr>212</StatusNbr>

          <StatusDescription>Employee information Found</StatusDescription>

        </ServiceStatus>

        <Result>

          <Empno>6263</Empno>

          <Ename>ABC</Ename>

          <Job>Manager</Job>

          <DOJ>10/10/2014 6:35:12 PM</DOJ>

        </Result>

      </GetData>

    </GetResponse>

  </WebServiceResponse>');

  v_result clob;

 v.Date               date;

CURSOR C1 IS

SELECT

  Q.LICENSESTATUSCODE   LICENSESTATUSCODE,

  Q.LICENSESTATUS       LICENSESTATUS,

  Q.LICENSEACTIONCODE   LICENSEACTIONCODE,

  Q.LICENSEACTION       LICENSEACTION,

  Q.REMAININGHITS       REMAININGHITS,

  Q.AMOUNT              AMOUNT,

  S.STATUSNBR           STATUSNBR,

  S.STATUSDESCRIPTION   STATUSDESCRIPTION,

  V.Empno             Empno,

  V.Ename             Ename,

  V.Job                 Job,

  v.DOJ                 DOJ

     From temp_xml T

    LEFT JOIN XMLTABLE('/WebServiceResponse'

                      PASSING T.FILE_CONTENT 

                      COLUMNS   LICENSESTATUSCODE     VARCHAR2(500)   PATH 

'SubscriptionInfo/LicenseStatusCode',

                                LICENSESTATUS         VARCHAR2(500)   PATH 

'SubscriptionInfo/LicenseStatus',

                                LicenseActionCode     VARCHAR2(500)   PATH 

'SubscriptionInfo/LicenseActionCode',

                                LicenseAction         VARCHAR2(500)   PATH 

'SubscriptionInfo/LicenseAction',

                                REMAININGHITS         VARCHAR2(500)   PATH 

'SubscriptionInfo/RemainingHits',

                                AMOUNT                VARCHAR2(500)   PATH 

'SubscriptionInfo/Amount',

                                SERVICESTATUS         XMLTYPE         PATH 

'GetResponse/GetData/ServiceStatus',

                                Result                Xmltype         Path 

'GetResponse/GetData/Result'

                                ) Q

    On (1=1) 

  Left Join Xmltable('/ServiceStatus'

                      Passing Q.ServiceStatus

                      COLUMNS

                      STATUSNBR           VARCHAR2(500) PATH 'StatusNbr'

                      ,StatusDescription  Varchar2(500) Path 'StatusDescription'

                      ) S

    On (1=1)

   Left Join Xmltable('/ServiceResult'

                      PASSING Q.SERVICERESULT

                      COLUMNS

                       Empno        VARCHAR2(500) PATH 'Empno'

                      ,Ename        VARCHAR2(500) PATH 'Ename'

                      ,Job          VARCHAR2(500) PATH 'Job'

                      ,DOJ          Varchar2(500) Path 'DOJ'                      

                      ) V

    ON (1=1)     

Where Nvl(Q.LicenseStatusCode,'~')<> '~';

  

BEGIN

BEGIN

DELETE FROM TEMP_XML;

commit;

SELECT REPLACE((REPLACE(((REPLACE

(V_XML,'xmlns="http://ws.abc.com"','+'))),'xmlns="http://www.abc.com"','+')),' +') INTO V_RESULT 

FROM DUAL;

INSERT INTO TEMP_XML

(FILE_CONTENT)

VALUES(XMLTYPE(V_RESULT));

COMMIT;

END;

BEGIN

FOR I IN C1

loop

v_Date:=to_date(i.date1, 'dd/mm/yyyy hh:mi:ss am');

INSERT INTO TEMP_SUBSCRIPTION

 ( 

LICENSESTATUSCODE,LICENSESTATUS,LICENSEACTIONCODE,LICENSEACTION,REMAININGHITS,AMOUNT,STATUSNBR,STA

TUSDESCRIPTION,Empno,Ename,Job,DOJ) 

VALUES ( 

I.LICENSESTATUSCODE,I.LICENSESTATUS,I.LICENSEACTIONCODE,I.LICENSEACTION,I.REMAININGHITS,I.AMOUNT,I

.STATUSNBR,I.STATUSDESCRIPTION,I.Empno,I.Ename,I.Job,v_date); 

end loop;

EXCEPTION WHEN OTHERS THEN

raise_application_error(-20002,'Err');

END;

commit;

END;