How to Import Data (to Oracle RDBMS) from XML

I wrote a blog post about how to import data from a XML file to Oracle about 7 years ago. I demonstrated how we can use DBMS_XMLSTORE package to parse XML data. Yesterday, I replied a question from a blog reader about that post. He had problem with running my sample script, I did troubleshoot and find out the problem with his script, and while testing the sample script, I noticed that I can write much better script.

This is my sample XML (employees.xml):

I created a table to load the data and a directory link so we can access the file:

Instead of using DBMS_XMLSTORE, I decided to use XMLType functions ExtractValue and XMLSequence. So I modified my old sample script and wrote this one:

You can compare it with my previous one. As you can see, I removed DBMS_XMLSTORE functions and write a simple SQL to fetch data from a CLOB which contains XML data. The main process is just an INSERT query but there are still some ugly stuff because I need to deal with reading file to the CLOB variable.

Then I realized that I could just remove all the CLOB stuff. In fact, a simple SQL can handle all the task:

This code will read from employees.xml file in XMLTEST directory, parse the data (the rowtag is EMPLOYEE, the fields are NO and NAME) and insert it into the EMPFROMXML table.

xmltype

Very clean and simple!

Please share
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases, and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

4 Comments

  1. Pavan

    Thank you, the above solution worked for me.

    But still surprised why the DBMS_XMLSTORE did not work.

    • Gokhan Atil

      Pavan,

      I could make it work with your sample XML. You should save the file with right encoding (“UTF-16 no BOM” worked for me), you should use uppercase tags (ie “FIRSTNAME” instead of “firstname”), and you should create a table which has all fields of XML data.

  2. Madhuri

    Hi,

    I’m trying to load nested XML data into an Oracle table. Would this work for my requirement as well? Are there any limitations to the size of the XML file? Does this require an XML schema to be set up in my database?

    Thanks

    -Madhuri

    • Gokhan Atil

      If you want to insert XML directly to one column, you don’t need to parse. All you need is to read from a bfile and write it.

      Maximum size of XML file is 4 GB because I use “bfile” datatype to read it.

      You don’t need XML Schema to use it but XML Schema is useful to validate XML documents.

Leave Comment

Your email address will not be published. Required fields are marked *