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;
