How to Import Data from XML

January 13, 2009Gokhan Atil16 CommentsOracledata, import, xml

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:

&lt;EMPLOYEES&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;1&lt;/NO&gt;
&lt;NAME&gt;Gokhan Atil&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;2&lt;/NO&gt;
&lt;NAME&gt;Ned Flanders&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;3&lt;/NO&gt;
&lt;NAME&gt;Agnes Skinner&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;EMPLOYEE&gt;
&lt;NO&gt;4&lt;/NO&gt;
&lt;NAME&gt;Milhouse Van Houten&lt;/NAME&gt;
&lt;/EMPLOYEE&gt;
&lt;/EMPLOYEES&gt;

<NAME>Gokhan Atil</NAME>

</EMPLOYEE>

<NAME>Ned Flanders</NAME>

</EMPLOYEE>

<NAME>Agnes Skinner</NAME>

</EMPLOYEE>

<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:

CREATE TABLE empfromxml ( no NUMBER, name VARCHAR2(50 ));

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:

CREATE DIRECTORY xmltest AS '/oracle/xmltest';

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:

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;
/

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:

SELECT * FROM empfromxml;

NO NAME
--------------------------------------------------
1 Gokhan Atil
2 Ned Flanders
3 Agnes Skinner
4 Milhouse Van Houten

SELECT * FROM empfromxml;

NO NAME

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

1 Gokhan Atil

2 Ned Flanders

3 Agnes Skinner

4 Milhouse Van Houten

Please share

Gokhan Atil

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

16 Comments

물어보고싶다

January 16, 2014 Reply

hi i can’t speak english.

i beginner

set serveroutput start and error

ora-31011, ora-19202, lpx-00210, ora-06512

why ?
- king
  
  October 8, 2015
  
  write
  set serveroutput on
Raji

April 14, 2016 Reply

Hi,

I am planning to use the above mentioned method to import xml file, but the file is located on windows client not on server. Can you please let me know a work around to read the file from client machine. I know Oracle procedure access only the server directories. I don’t have privileges to ftp the file.
Kamal KHELIFI

June 15, 2016 Reply

hello

great article,

so we need more information about how to use this methode for making or for built an mobil application with oracle database 11GR2

Thanks
Pavan

August 24, 2016 Reply

I tried the above procedure and getting this error.

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected ‘<‘ instead of ‘?’
ORA-06512: at “SYS.DBMS_XMLSTORE”, line 78
ORA-06512: at line 18

Suggest me a solution.
- Gokhan Atil
  
  August 24, 2016
  
  In the sample file, there’s no question mark but in your error message, it says it found a question mark while expecting “<" sign. It could be related with character encoding of your XML file.
Pavan

August 25, 2016 Reply

the process gets completed but no rows are inserted.

DECLARE

xml_handle DBMS_XMLSTORE.ctxtype;

xml_file BFILE;

number_of_rows NUMBER;

xml_data CLOB;

BEGIN

xml_file := BFILENAME (‘XMLTEST’, ’emp_testdata.xml’);

DBMS_LOB.createtemporary (xml_data, TRUE, DBMS_LOB.SESSION);

DBMS_LOB.fileopen (xml_file, DBMS_LOB.file_readonly);

DBMS_LOB.loadfromfile (xml_data, xml_file, DBMS_LOB.getlength(xml_file));

DBMS_LOB.fileclose (xml_file);

xml_handle := DBMS_XMLSTORE.newcontext (‘DATA_FROM_XML_FILE’);

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;

/
- Gokhan Atil
  
  August 25, 2016
  Hi Pavan,
  
  Did you created the DATA_FROM_XML_FILE table? It is the table you want to import data according to your sample code.
  
  CREATE TABLE DATA_FROM_XML_FILE ( emplid NUMBER, firstname VARCHAR2(50), lastname VARCHAR2(50), age number, email VARCHAR2(100) );
Pavan

August 25, 2016 Reply

This is the data.

<?xml version=”1.0″?>

-<Employees>
-<Employee>

<emplid>1111</emplid>

<firstname>John</firstname>

<lastname>Watson</lastname>

<age>30</age>

<email>johnwatson@sh.com</email>

</Employee>
-<Employee>

<emplid>2222</emplid>

<firstname>Sherlock</firstname>

<lastname>Homes</lastname>

<age>32</age>

<email>sherlock@sh.com</email>

</Employee>

</Employees>
- Gokhan Atil
  
  August 25, 2016
  You need to remove the first line (xml version), and make sure that XML tags are uppercase!
  
  For example:
  
  <EMPLOYEES> <EMPLOYEE> <EMPLID>1111</EMPLID> <FIRSTNAME>John</FIRSTNAME> <LASTNAME>Watson</LASTNAME> <AGE>30</AGE> <EMAIL>johnwatson@sh.com</EMAIL> </EMPLOYEE> <EMPLOYEE> <EMPLID>2222</EMPLID> <FIRSTNAME>Sherlock</FIRSTNAME> <LASTNAME>Homes</LASTNAME> <AGE>32</AGE> <EMAIL>sherlock@sh.com</EMAIL> </EMPLOYEE> </EMPLOYEES>
Pingback: How to Import Data (to Oracle RDBMS) from XML | Gokhan Atil's Oracle Blog
Pavan

August 26, 2016 Reply

I tried all the things suggested . But still no records are being inserted. The process completes successfully with out any exceptions.

Please suggest any solution.
- Gokhan Atil
  
  August 26, 2016
  
  Please try another solution (much easier): http://www.gokhanatil.com/2016/08/how-to-import-data-to-oracle-rdbms-from-xml.html
saket naidu

September 7, 2016 Reply

very useful…
Harun Tok

January 17, 2017 Reply

Hi Mr. Atil,

is it possible to import complex xml-structures into oracle xmldb on this way?

For example:

xml-structure …

…

<contactdata>

<name> hugo <\name>

<adress1> van den bosch street <\adress1>

<adress2> trophy street <\adress2>

<business1> health <\businnes1>

<business2> beauty <\business2>

…

…

<\contactdata>

the complexity is a deep data structure to import all the information … into oracle external tables …

Best regards,

Harun
David Loaiza

March 13, 2017 Reply

Hi, great post but what if I’m using a table with a XMLTYPE and not a file.

Leave Comment Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.