Home » Developer & Programmer » JDeveloper, Java & XML » How to Read Xml Data into Oracle Database (Oracle 10g, Windows 7)
icon5.gif  How to Read Xml Data into Oracle Database [message #628669] Wed, 26 November 2014 22:02 Go to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Hi,

I have some XML Files which has some data like user informations etc., which is in a specific folder or drive D:\, well i want to count the number of xml files from that specific folder and based on that i need to Load the xml data from the files into a Oracle Table, how can we achieve this?

I am new to xml and this type of work i didnt tried before.

pls give your valuable ideas so that i can implement.

Thank you...
Re: How to Read Xml Data into Oracle Database [message #628671 is a reply to message #628669] Wed, 26 November 2014 22:41 Go to previous messageGo to next message
SunilJdh
Messages: 17
Registered: October 2014
Location: India
Junior Member

I do it This Way:
/*-- Reading XML File have to create Directory 'TEST_DIR' --*/

declare xDoc xmltype := xmltype(BFILENAME('TEST_DIR','TestData.xml' ),('AL32UTF8'));
rc_xml sys_refcursor;
begin
open rc_xml for select xt.* from xmltable('//Root/Data'
passing xDoc
column
"ColumnName" datatype Path 'ElementName',
"ColumnName" datatype Path 'ElementName') XT;
end;

[Updated on: Wed, 26 November 2014 22:43]

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628682 is a reply to message #628669] Thu, 27 November 2014 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just the last topic in our JDeveloper, Java & XML forum: Loading XML in Oracle table
Re: How to Read Xml Data into Oracle Database [message #628719 is a reply to message #628671] Thu, 27 November 2014 06:28 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
SunilJdh wrote on Thu, 27 November 2014 11:41
I do it This Way:
/*-- Reading XML File have to create Directory 'TEST_DIR' --*/

declare xDoc xmltype := xmltype(BFILENAME('TEST_DIR','TestData.xml' ),('AL32UTF8'));
rc_xml sys_refcursor;
begin
open rc_xml for select xt.* from xmltable('//Root/Data'
passing xDoc
column
"ColumnName" datatype Path 'ElementName',
"ColumnName" datatype Path 'ElementName') XT;
end;


Hi thanks for the reply, but actually we have more than 10 folders/drives from different servers so how to loop that and fetch those xml files data into a DB Table or tables.

[Updated on: Thu, 27 November 2014 06:28]

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628726 is a reply to message #628719] Thu, 27 November 2014 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

For security reason, Oracle does not (and should not) read files on other servers.

Re: How to Read Xml Data into Oracle Database [message #628752 is a reply to message #628719] Fri, 28 November 2014 01:07 Go to previous messageGo to next message
SunilJdh
Messages: 17
Registered: October 2014
Location: India
Junior Member

Probably you should pass parameter from front-end application and do all looping there.
Re: How to Read Xml Data into Oracle Database [message #628788 is a reply to message #628726] Fri, 28 November 2014 07:52 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
Michel Cadot wrote on Thu, 27 November 2014 20:16

For security reason, Oracle does not (and should not) read files on other servers.



Actually concept is not like that, we will have a individual server only,
which has a Drive(eg: D:\folder1\sh1.xml) containing folders which hold
xml files so just the concept is to find each folders which containing the xml files.

The need is that we have not only one files but different files from different folders,
but in a specific drive, a folder has more than one files.

example:

d:\folder1\sh1.xml
d:\folder1\sh2.xml
d:\folder2\w1.xml
d:\folder2.w1.xml
Re: How to Read Xml Data into Oracle Database [message #628824 is a reply to message #628788] Fri, 28 November 2014 11:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following provides references to two different methods of obtaining file names from an operating system directory. You can then loop through those, selecting only the xml files, then do whatever you want with those files.

http://www.williamrobertson.net/documents/plsql-list-files.html
Re: How to Read Xml Data into Oracle Database [message #628895 is a reply to message #628824] Sun, 30 November 2014 06:48 Go to previous messageGo to next message
stalin4d
Messages: 226
Registered: May 2010
Location: Chennai, Tamil Nadu, Indi...
Senior Member
I run this below code in my Plsql work area i am getting an error;

SELECT logdate, empcode
      FROM (SELECT XMLTYPE(bfilename('XMLSURYA', 'Sample1.xml'), 
                            nls_charset_id('UTF8')) xml_data
              FROM dual),
           XMLTable('for $i in /EVENTID
                                return $i'
                    passing xml_data
                    columns logdate DATE path '@LOGDATE',
                            EMPCODE VARCHAR2(50) path '@EMPCODE')


Error:

ORA-22288 file or LOB operation FILEOPEN failed


may i know what would be the error and how to overcome?
Re: How to Read Xml Data into Oracle Database [message #628897 is a reply to message #628895] Sun, 30 November 2014 07:25 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-22288: file or LOB operation %s failed\n%s
 *Cause: The operation attempted on the file or LOB failed.
 *Action: See the next error message in the error stack for more detailed
          information.  Also, verify that the file or LOB exists and that
          the necessary privileges are set for the specified operation. If
          the error still persists, report the error to the DBA.
Previous Topic: Build Problem with JDeveloper - Please Help
Next Topic: Sql query for the unexpected scenario
Goto Forum:
  


Current Time: Thu Mar 28 11:56:33 CDT 2024