One of my blog readers asked me how to import data from XML file. There are lots of ways to do it but I’ll show only a simple one by using DBMS_XMLSTORE.
We will need a simple XML file to import. So I created a folder as “/oracle/xmltest” and a text file named (sample.xml) in this folder. Oracle should be able to access this folder and file, so check OS permissions of them and be sure that they are readable by oracle user.
I entered the followed data into my sample xml file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<EMPLOYEES> <EMPLOYEE> <NO>1</NO> <NAME>Gokhan Atil</NAME> </EMPLOYEE> <EMPLOYEE> <NO>2</NO> <NAME>Ned Flanders</NAME> </EMPLOYEE> <EMPLOYEE> <NO>3</NO> <NAME>Agnes Skinner</NAME> </EMPLOYEE> <EMPLOYEE> <NO>4</NO> <NAME>Milhouse Van Houten</NAME> </EMPLOYEE> </EMPLOYEES> |
As you see, our employees have only 2 properties, “no” and “name”. So I’ll create a table to store these properties:
|
1 |
CREATE TABLE empfromxml ( no NUMBER, name VARCHAR2(50 )); |
Because we’ll read from a file, we need to create a directory object for the folder which our XML files are located:
|
1 |
CREATE DIRECTORY xmltest AS '/oracle/xmltest'; |
By default, XML documents are expected to identify rows with the <ROW> tag. This is the same default used by DBMS_XMLGEN when generating XML. This may be overridden by calling the setrowtag function. Here’s our anonymous PLSQL block to read this file:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
SET SERVEROUTPUT ON DECLARE xml_handle DBMS_XMLSTORE.ctxtype; f BFILE; number_of_rows NUMBER; xml_data CLOB; BEGIN f := BFILENAME ('XMLTEST', 'sample.xml'); DBMS_LOB.createtemporary (xml_data, TRUE, DBMS_LOB.SESSION); DBMS_LOB.fileopen (f, DBMS_LOB.file_readonly); DBMS_LOB.loadfromfile (xml_data, f, DBMS_LOB.getlength (f)); DBMS_LOB.fileclose (f); xml_handle := DBMS_XMLSTORE.newcontext ('EMPFROMXML'); DBMS_XMLSTORE.setrowtag (xml_handle, 'EMPLOYEE'); number_of_rows := DBMS_XMLSTORE.insertxml (xml_handle, xml_data); DBMS_OUTPUT.PUT_LINE( number_of_rows || ' rows inserted.' ); DBMS_XMLSTORE.closecontext (xml_handle); DBMS_LOB.freetemporary (xml_data); COMMIT; END; / |
After we run the above PLSQL block to import data from XML file, we can query “empfromxml” table:
|
1 2 3 4 5 6 7 8 |
SELECT * FROM empfromxml; NO NAME -------------------------------------------------- 1 Gokhan Atil 2 Ned Flanders 3 Agnes Skinner 4 Milhouse Van Houten |
