OraFAQ Forum: JDeveloper, Java & XML » How to Read Xml Data into Oracle Database

Home » Developer & Programmer » JDeveloper, Java & XML » How to Read Xml Data into Oracle Database (Oracle 10g, Windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to Read Xml Data into Oracle Database [message #628669]

Wed, 26 November 2014 22:02

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...

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628671 is a reply to message #628669]

Wed, 26 November 2014 22:41

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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Just the last topic in our JDeveloper, Java & XML forum: Loading XML in Oracle table

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628719 is a reply to message #628671]

Thu, 27 November 2014 06:28

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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628752 is a reply to message #628719]

Fri, 28 November 2014 01:07

SunilJdh
Messages: 17
Registered: October 2014
Location: India

Junior Member

Probably you should pass parameter from front-end application and do all looping there.

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628788 is a reply to message #628726]

Fri, 28 November 2014 07:52

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

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628824 is a reply to message #628788]

Fri, 28 November 2014 11:54

Barbara Boehmer
Messages: 9103
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

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628895 is a reply to message #628824]

Sun, 30 November 2014 06:48

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?

Report message to a moderator

Re: How to Read Xml Data into Oracle Database [message #628897 is a reply to message #628895]

Sun, 30 November 2014 07:25

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Previous Topic:	Build Problem with JDeveloper - Please Help
Next Topic:	Sql query for the unexpected scenario

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 01:12:16 CST 2025