How to Import Data from 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:

As you see, our employees have only 2 properties, “no” and “name”. So I’ll create a table to store these properties:

Because we’ll read from a file, we need to create a directory object for the folder which our XML files are located:

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:

After we run the above PLSQL block to import data from XML file, we can query “empfromxml” table:

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. 물어보고싶다

    hi i can’t speak english.

    i beginner

    set serveroutput start and error

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

    why ?


  2. Raji


    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.

  3. Kamal KHELIFI


    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


  4. Pavan

    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

      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.

  5. Pavan

    the process gets completed but no rows are inserted.



    xml_handle DBMS_XMLSTORE.ctxtype;

    xml_file BFILE;

    number_of_rows NUMBER;

    xml_data CLOB;


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





    • Gokhan Atil

      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.

      firstname VARCHAR2(50),
      lastname VARCHAR2(50),
      age number, 
      email VARCHAR2(100) 
  6. Pavan

    This is the data.


    <?xml version=”1.0″?>















    • Gokhan Atil

      You need to remove the first line (xml version), and make sure that XML tags are uppercase!

      For example:

  7. Pingback: How to Import Data (to Oracle RDBMS) from XML | Gokhan Atil's Oracle Blog

  8. Pavan

    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.

  9. Harun Tok

    Hi Mr. Atil,

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


    For example:

    xml-structure …


    <name> hugo <\name>

    <adress1> van den bosch street <\adress1>

    <adress2> trophy street <\adress2>

    <business1> health <\businnes1>

    <business2> beauty <\business2>


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

    Best regards,




  10. David Loaiza

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

Leave Comment

Your email address will not be published.

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