Wednesday 15 October 2014

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;


1 comment: