PL/SQL APIs for XMLType
Before we get started,i want to let you know that i need to parsing following different shape of xml.Has got two different type of xml such as tag based and attribute based and i have to parse and load into table for reporting whole data,there are lots of instances for parse and load data to table with tag based xml but attribute based xml absent in the internet hence i want to share this article.
<?xml version="1.0" encoding="windows-1254" standalone="no"?>
<DATAPACKETVersion="1.0">
<METADATA>
<FIELDS>
<FIELD ATTRNAME="EMPLOYEE_NUMBER"FIELDTYPE="IBString"WIDTH="6196"/>
<FIELD ATTRNAME="FIRSTNAME"FIELDTYPE="IBString"WIDTH="6196"/>
<FIELD ATTRNAME="LASTNAME"FIELDTYPE="IBString"WIDTH="6196"/>
<FIELD ATTRNAME="HIRE_DATE"FIELDTYPE="Date"WIDTH="6196"/>
<FIELD ATTRNAME="TITLE"FIELDTYPE="IBString"WIDTH="6196"/>
<FIELD ATTRNAME="DIVISION"FIELDTYPE="IBString"WIDTH="6196"/>
<FIELD ATTRNAME="BUILDING"FIELDTYPE="Largeint"WIDTH="6196"/>
<FIELD ATTRNAME="SALARY"FIELDTYPE="FMTBCD"WIDTH="6196"/>
<FIELD ATTRNAME="ROOM"FIELDTYPE="Largeint"WIDTH="6196"/>
<FIELD ATTRNAME="SUPERVISOR"FIELDTYPE="IBString"WIDTH="6196"/>
</FIELDS>
</METADATA>
<ROWDATA>
<ROW EMPLOYEE_NUMBER="be129"FIRSTNAME="Jane"LASTNAME="Doe"HIRE_DATE="23.01.2009"TITLE="Engineer"DIVISION="Materials"BUILDING="327"SALARY="4323,4"ROOM="19"SUPERVISOR="be131"/>
<ROW EMPLOYEE_NUMBER="be130"FIRSTNAME="William"LASTNAME="Defoe"HIRE_DATE="17.04.2013"TITLE="Accountant"DIVISION="Accts Payable"BUILDING="326"SALARY="2223,2"ROOM="14a"SUPERVISOR="a112"/>
<ROW EMPLOYEE_NUMBER="be131"FIRSTNAME="Jack"LASTNAME="Dee"HIRE_DATE="01.01.2002"TITLE="Engineering Manager"DIVISION="Materials"BUILDING="327"SALARY="10120,9"ROOM=" "SUPERVISOR="db311"/>
<ROW EMPLOYEE_NUMBER="be132"FIRSTNAME="Sandra"LASTNAME="Rogers"HIRE_DATE="03.09.2016"TITLE="Engineer"DIVISION="Materials"BUILDING="327"SALARY="3000,5"ROOM="19"SUPERVISOR="be131"/>
<ROW EMPLOYEE_NUMBER="be133"FIRSTNAME="Steve"LASTNAME="Casey"HIRE_DATE="09.05.2012"TITLE="Engineer"DIVISION="Materials"BUILDING="327"SALARY="5323,6"ROOM="19"SUPERVISOR="be129"/>
</ROW DATA>
</DATAPACKET>
let's get to the point,let's hold the reason.I create the following table for loading data from xml file.
DROP TABLE H_KURU.FROM_XML_TO_TABLE_DATA CASCADE CONSTRAINTS;
CREATE TABLE H_KURU.FROM_XML_TO_TABLE_DATA
(
EMPLOYEE_NUMBER VARCHAR2(30BYTE),
FIRSTNAME VARCHAR2(30BYTE),
LASTNAME VARCHAR2(30BYTE),
HIRE_DATE DATE,
TITLE VARCHAR2(30BYTE),
DIVISION VARCHAR2(30BYTE),
BUILDING NUMBER,
SALARY NUMBER,
ROOM NUMBER,
SUPERVISOR VARCHAR2(30BYTE)
);
/
I could not create a new oracle directory for keep to xml file,I used standart "XMLDIR" directory if you want you can create a new directory.I'm on the right track now i'll put into XMLDIR directory path my xml files ,in my pc this directory path as follows.
'C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\xml';
GRANT READ, WRITE ON DIRECTORY XMLDIR TO H_KURU WITH GRANT OPTION;
the above command required to reading xml files from procedure.
CREATE OR REPLACE PROCEDURE H_KURU.load_from_xml_file_to_table
IS
l_bfile BFILE;
l_clob CLOB;
l_parser DBMS_XMLPARSER.Parser;
l_doc DBMS_XMLDOM.DOMDocument;
l_nl DBMS_XMLDOM.DOMNodeList;
l_n DBMS_XMLDOM.DOMNode;
TYPE tab_type IS TABLE OF from_xml_to_table_data%ROWTYPE;
t_tab tab_type := tab_type ();
BEGIN
SAVEPOINT svp_ini;
l_bfile := BFILENAME ('XMLDIR', 'attributeBasedXml.xml');
DBMS_LOB.createtemporary (l_clob, cache => FALSE);
DBMS_LOB.open (l_bfile, DBMS_LOB.lob_readonly);
DBMS_LOB.loadFromFile (dest_lob => l_clob,
src_lob => l_bfile,
amount => DBMS_LOB.getLength (l_bfile));
DBMS_LOB.close (l_bfile);
-- make sure implicit date conversions are performed correctly
DBMS_SESSION.set_nls ('NLS_DATE_FORMAT', '''DD.MM.YYYY''');
-- Create a parser.
l_parser := DBMS_XMLPARSER.newParser;
-- Parse the document and create a new DOM document.
DBMS_XMLPARSER.parseClob (l_parser, l_clob);
l_doc := DBMS_XMLPARSER.getDocument (l_parser);
-- Free resources associated with the CLOB and Parser now they are no longer needed.
DBMS_LOB.freetemporary (l_clob);
DBMS_XMLPARSER.freeParser (l_parser);
-- Get a list of all the EMP nodes in the document using the XPATH syntax.
l_nl := DBMS_XSLPROCESSOR.selectNodes (DBMS_XMLDOM.makeNode (l_doc),'DATAPACKET/ROWDATA/ROW');
-- Loop through the listand create a new record in a tble collection
-- for each EMP record.
FOR cur_emp IN 0 .. DBMS_XMLDOM.getLength (l_nl) - 1
LOOP
l_n := DBMS_XMLDOM.item (l_nl, cur_emp);
t_tab.EXTEND;
-- UseXPATHsyntaxtoassignvaluestoheelementsofthecollection.
DBMS_XSLPROCESSOR.valueOf (l_n,'@EMPLOYEE_NUMBER',t_tab (t_tab.LAST).employee_number);
DBMS_XSLPROCESSOR.valueOf (l_n,'@FIRSTNAME',t_tab (t_tab.LAST).firstname);
DBMS_XSLPROCESSOR.valueOf (l_n,'@LASTNAME',t_tab (t_tab.LAST).lastname);
DBMS_XSLPROCESSOR.valueOf (l_n,'@HIRE_DATE',t_tab (t_tab.LAST).hire_date);
DBMS_XSLPROCESSOR.valueOf (l_n,'@TITLE', t_tab (t_tab.LAST).title);
DBMS_XSLPROCESSOR.valueOf (l_n,'@DIVISION', t_tab (t_tab.LAST).division);
DBMS_XSLPROCESSOR.valueOf (l_n,'@BUILDING', t_tab (t_tab.LAST).building);
DBMS_XSLPROCESSOR.valueOf (l_n,'@SALARY',t_tab (t_tab.LAST).salary);
DBMS_XSLPROCESSOR.valueOf (l_n,'@ROOM',t_tab (t_tab.LAST).room);
DBMS_XSLPROCESSOR.valueOf (l_n,'@SUPERVISOR',t_tab (t_tab.LAST).supervisor);
END LOOP;
-- Insert data into the real from_xml_to_table_data table from the table collection.
FORALL i IN t_tab.FIRST .. t_tab.LAST
INSERT INTO from_xml_to_table_data
VALUES t_tab (i);
COMMIT;
-- Free any resources associated with the document now it
-- is no longer needed.
DBMS_XMLDOM.freeDocument (l_doc);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_LOB.freetemporary (l_clob);
DBMS_XMLPARSER.freeParser (l_parser);
DBMS_XMLDOM.freeDocument (l_doc);
ROLLBACK TO svp_ini;
END;
/
As a result if i execute above procedure and then call my data table it would be generate the following output.
i hope this article would be usefull when you are need parse and load header,line based xml files to table data.
with my respects..