1 Reply Latest reply: Jan 7, 2013 11:44 PM by 937360 RSS

    how to load xml data into a table

    982685
      Hi,

      i am a newbie. I want to insert the data of xml file into a table. I am doing this using XSU api for java.
      I am using oracle 9i and jdk 1.7.
      I am using OracleXmlSave class.

      but i am getting following error.

      java.lang.NoClassDefFoundError: oracle/jdbc2/Clob


      Please help in this regard. this is my first thread.

      thanks.

      Edited by: 979682 on Jan 3, 2013 3:39 AM
        • 1. Re: how to load xml data into a table
          937360
          Hi,
          You can insert XML data from XML file to Oracle database by this script :

          Hi,
          For reading and inserting the data from XML file to Oracle Database :


          1. CREATE A BLANK TABLE with same structure as XML file :
          select * from xml_test


          2. SELECT QUERY DIRECTLY ON XML FILE :
          SELECT XMLTYPE(bfilename('TEST_DIR', 'data_file.xml'), nls_charset_id('UTF8')) xml_data FROM dual


          3. CREATE ORACLE DIRECTORY AND PLACE XML FILE IN THIS DIRECTORY LOCATION:

          --CREATE DIRECTORY TEST_DIR as '/oracle/test';
          --grant all on directory TEST_DIR to public;


          4. INSERT THE XML DATA IN ORACLE TABLE:
          INSERT INTO xml_test(column1,coumn2)
          WITH t AS (SELECT XMLTYPE(bfilename('TEST_DIR', 'attachment.xml'), nls_charset_id('UTF8')) xml_col FROM dual)
          SELECT
          extractValue(value(x),'/ROW/COLUMN1') column1
          ,extractValue(value(x),'ROW/COLUMN2') column2
          FROM t,TABLE(XMLSequence(extract(t.xml_col,'/ROWSET/ROW'))) x;

          I have assumed a table with 2 columns.


          Regards,
          Rohit Chaudhari
          rohit1304@live.com