Multilevel XML To Multiple Table

Recently I was working on an implementation project and was faced with situation where client wanted some inbound data populate standard tables. Fair enough, but the problem was that the data provided was in XML format and they wanted a PL/SQL solution for it.
After some research I managed to find out lot of APIs provided by Oracle (and there are plenty of them). Oracle 9i has DBMS_XMLSave
while Oracle 10g has DBMS_XMLStore .
DBMS_XMLStore is the preferred package as it is written in C and linked into the Oracle kernel. It also uses the SAX parser. As a result DBMS_XMLStore has better performance characteristics.

Both of these APIs rest over XMLtype object. XMLType is a system-defined opaque type for handling XML data and has predefined member functions on it to extract XML nodes and fragments. You can create columns of XMLType and insert XML documents into it. You can also generate XML documents as XMLType instances dynamically.

Now, most samples that I found on internet handled inserting into a single table at a time (will show that later as well). However, what I needed was
1) Read XML file from a file known location.
2) Read the structure into a master-detail (parent-child) kind of relation.

Based on my learnings, here is what it looks like :


-- In -- Flat Multi Level XML
-- Out -- Two Tables

----------------------------------
TABLES
----------------------------------
CREATE TABLE emp_t (
EMP_ID NUMBER,
NAME VARCHAR2(10) )
/
CREATE TABLE emp_details (
EMP_ID NUMBER,
DESCRIPTION VARCHAR2(100),
B_DATE DATE)
/
----------------------------------
XML
----------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<EMP_ROWS>
<EMP>
<EMP_ID>1</EMP_ID>
<NAME>A</NAME>
<LINE_ROWS>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION1</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION2</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION3</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>1</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTIONDESCRIPTION4</DESCRIPTION>
</LINE>
</LINE_ROWS>
</EMP>
<EMP>
<EMP_ID>2</EMP_ID>
<NAME>B</NAME>
<LINE_ROWS>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION5</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION6</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION7</DESCRIPTION>
</LINE>
<LINE>
<EMP_ID>2</EMP_ID>
<B_DATE>01-JUN-2005</B_DATE>
<DESCRIPTION>DESCRIPTION8</DESCRIPTION>
</LINE>
</LINE_ROWS>
</EMP>
</EMP_ROWS>


----------------------------------
Sample Code
----------------------------------

DECLARE

v_xml_clob CLOB;
-- Oracle directory object and filename
-- Read the XML file into BFILE

v_xml_file BFILE := BFILENAME('USR_TMP', 'emp_data_levels.xml');

-- offsets refer to start of files
v_dst_offset number := 1;
v_src_offset number := 1;

v_lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
v_warning number;

v_xml XMLtype;

v_rows NUMBER;

BEGIN
-- Open xml file on OS
dbms_lob.open(v_xml_file,dbms_lob.lob_readonly);

-- necessary for correct handling of LOB locator
dbms_lob.createtemporary(v_xml_clob, true, dbms_lob.session);

-- xml file to clob
dbms_lob.loadCLOBfromfile(
dest_lob => v_xml_clob
, src_bfile => v_xml_file
, amount => dbms_lob.getlength(v_xml_file)
, dest_offset => v_dst_offset
, src_offset => v_src_offset
, bfile_csid => dbms_lob.default_csid
, lang_context => v_lang_ctx
, warning => v_warning
);

-- close xml file
dbms_lob.close(v_xml_file);

-- clob to XMLtype
v_xml := XMLtype(v_xml_clob);

------------------------------------
-- XML Processing Begins
------------------------------------
INSERT
WHEN tab = 'emp_t'
THEN
INTO emp_t
VALUES (id, name)
WHEN tab = 'emp_details'
THEN
INTO emp_details
VALUES (id, b_date, description)
WITH T AS (select XMLtype(v_xml_clob) xml from dual)
SELECT 'emp_t' tab,
to_number(extractvalue (t2.column_value, 'EMP/EMP_ID')) id,
extractvalue (t2.column_value, 'EMP/NAME') name,
null b_date,
null description
FROM t t, TABLE (xmlsequence (t.xml.extract ('EMP_ROWS/EMP'))) t2
UNION ALL
SELECT 'emp_details',
to_number(extractvalue (t2.column_value, 'LINE/EMP_ID')) emp_id,
null,
extractvalue (t2.column_value, 'LINE/DESCRIPTION') description,
to_date(extractvalue (t2.column_value, 'LINE/B_DATE'),'DD-MON-YYYY') b_date
FROM t t,
TABLE (xmlsequence (t.xml.extract ('EMP_ROWS/EMP/LINE_ROWS/LINE'))) t2;

-- commit ;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line ( 'Exception' || SQLERRM );
END;


------------------------------------------------------------------------

Code is mostly self explanatory. Read the physical file, create a temporary CLOB, load CLOB from file and then crete a new XMLType. Using XMLType and XPath notation, read the XML and insert into the required columns.
That's it !!!