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.


Very clean and simple!

Please share

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.


  1. Pavan

    Thank you, the above solution worked for me.

    But still surprised why the DBMS_XMLSTORE did not work.

    • Gokhan Atil


      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


    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?



    • 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.

  3. kailash s

    could you describe where is directory Path, <>

    (1) it’s will be possible to allow User Computer.
    (2) how can handle if client having complicated XML File suppose having sub-node inside Nodes.

  4. Giovanni

    so grateful for the content of this post… everything went fine by following your advices. thank you so much

Leave Comment

Your email address will not be published.

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