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

User_L3J2D

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]