Forum Stats

  • 3,827,343 Users
  • 2,260,762 Discussions
  • 7,897,209 Comments

Discussions

how to load xml data into a table

982685
982685 Member Posts: 1
edited Jan 8, 2013 12:44AM in General XML
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
Tagged:

Answers

  • User_L3J2D
    User_L3J2D Member Posts: 11 Blue Ribbon
    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
    [email protected]
This discussion has been closed.