This discussion is archived
1 Reply Latest reply: Jan 7, 2013 9:44 PM by 937360 RSS

how to load xml data into a table

982685 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points