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:

15 Responses to “How to Import Data from XML”

  1. 물어보고싶다 says:

    hi i can’t speak english.

    i beginner

    set serveroutput start and error

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

    why ?

     

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

  3. Kamal KHELIFI says:

    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

  4. 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 says:

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

      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.

  6. 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 says:

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

      For example:

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

  8. saket naidu says:

    very useful…

  9. Harun Tok says:

    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

     

  10. David Loaiza says:

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

Trackbacks/Pingbacks

  1. How to Import Data (to Oracle RDBMS) from XML | Gokhan Atil's Oracle Blog - […] wrote a blog post about how to import data from a XML file to Oracle about 7 years ago.…

Leave a Reply

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