How to transfer Data from Simple XML Sheet to Oracle Database?

It is the actual job of ETL Developer, which will fall into the general category here. In general, we all use some tools to do this job of transferring data from XML to Oracle DB. In fact Oracle itself has a tool, which will do the job with minimal steps.

XSU is the name of the utility, which can be expanded as XML to SQL Utility. There are in general two approaches to doing this.

- Advertisement -

1. PL/SQL
2. Java

I tried out the PL/SQL and succeeded. I am a dummy in Java, so I cannot help you out in that.

Foremost, make a note of the per-requisites.

This is the XML file I am considering.
<?xml version=”1.0″ encoding=”utf-8″ ?>
– <ROWSET>
S <ROW num = “1”>
<EMPLOYEE_ID> 7370 </EMPLOYEE_ID>
<FIRST_NAME>ORACLE</FIRST_NAME>
<LAST_NAME>VIRUS</LAST_NAME>
<SALARY>100000000</SALARY>
<PHONE_NO>555555</PHONE_NO>
<DEPT>BUSINESS_INTELLIGENCE</DEPT>
</ROW>
</ROWSET>

- Advertisement -

Now I will create a table in database with CLOB column.

CREATE TABLE xmldocument
(docid NUMBER PRIMARY KEY, xml_text CLOB);

Now dump this xml document into that table.

INSERT INTO XMLDOCUMENT VALUES
('1'. '<?xml version="1.0" encoding="utf-8" ?>
<ROWSET>
<ROW num = "1">
<EMPLOYEE_ID> 7370 </EMPLOYEE_ID>
<FIRST_NAME>ORACLE</FIRST_NAME>
<LAST_NAME>VIRUS</LAST_NAME>
<SALARY>100000000</SALARY>
<PHONE_NO>111111</PHONE_NO>
<DEPT>BUSINESS_INTELLIGENCE</DEPT>
</ROW>
</ROWSET>');

Now the xml document is in database which u just have to parse it into proper columns in a proper table.

Before that we have to create a stored procedure in the database as i did below:

CREATE OR REPLACE PROCEDURE proc_inc (xml_doc IN CLOB, table_name IN VARCHAR2)
IS
insctx dbms_xmlsave.ctxtype;
ROWS NUMBER;
BEGIN
ins_ctx := dbms_xmlsave.newcontext (table_name); -- get the context handle
ROWS := dbms_xmlsave.insertxml (insctx, xml_doc); -- this inserts the document
dbms_xmlsave.closecontext (ins_ctx); -- this closes the handle
END;
/

Now BEFORE passing these values, you must have an Employees table with the same structure. If any column is missing in XML doc, then it will insert NULL.

CREATE OR REPLACE TABLE EMPLOYEES(EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
PHONE_NO VARCHAR2(30),
SALARY NUMBER,
DEPT VARCHAR2(30));

Now the final step…proceed towards inserting data into oracle table.

DECLARE
v_xml_text CLOB;
BEGIN
SELECT xml_text
INTO v_xml_text
FROM XMLDOCUMENT
WHERE DOCID = 1;
INSPROC(V_XML_TEXT, 'employees');
END;
/

Now you can check the data in oracle table by querying it.

SELECT * FROM EMPLOYEES;

- Advertisement -