OraFAQ Forum: JDeveloper, Java & XML » Storing XML File In Server

Home » Developer & Programmer » JDeveloper, Java & XML » Storing XML File In Server (PL/SQL, Oracle 10g)

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

Storing XML File In Server [message #486866]

Mon, 20 December 2010 23:48

prasannamathan
Messages: 3
Registered: December 2010

Junior Member

Hi All,

I am new to this XML topic. My current task is to store XML
file from local system ( OS :XP)into server (OS : Linux) specified location with the help of PL/SQL procedure.

Below is the XML file,

<?xml version="1.0" encoding="ISO-8859-1" ?>
<EMPLOYEES>
<ItemGroupData ItemGroupOID="Emp1" ItemGroupRepeatKey="1">
<ItemData ItemOID="EMPNO" Value="11"/>
<ItemData ItemOID="ENAME" Value="aaa"/>
<ItemData ItemOID="JOB" Value="SE"/>
<ItemData ItemOID="MGR" Value="7902"/>
<ItemData ItemOID="HIREDATE" Value="20-OCT-80"/>
<ItemData ItemOID="SAL" Value="28000"/>
</ItemGroupData>
</EMPLOYEES>

Can any one kindly advise me how to complete this task.

Thanks in advance!!

Report message to a moderator

Re: Storing XML File In Server [message #486880 is a reply to message #486866]

Tue, 21 December 2010 01:15

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

Senior Member
Account Moderator

1/ Create a database
2/ Create a user/schema
3/ Create a table with at least a column for your xml
4/ Use DBMS_LOB.LOADCLOBFROMFIL to load the xml file into a PL/SQL variable
5/ Use INSERT to record it into the table

Regards
Michel

Report message to a moderator

Re: Storing XML File In Server [message #486963 is a reply to message #486880]

Tue, 21 December 2010 11:03

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

I don't believe that you are going to be able to load a file containing xml data from your local file system to the server using the dbms_lob package, but you can use SQL*Loader:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb25loa.htm

Report message to a moderator

Re: Storing XML File In Server [message #487128 is a reply to message #486963]

Thu, 23 December 2010 00:04

prasannamathan
Messages: 3
Registered: December 2010

Junior Member

Hi All,

Thanks for the reply.

I don't want to store the xml file data in the database from local machine i need store or move (image) the xml file from local machine to the server.Once this is done then i already wrote code to fetch the data from the xml file server machine location to store into the database.

Kindly give me some idea on this.

Regards

[Updated on: Thu, 23 December 2010 00:04]

Report message to a moderator

Re: Storing XML File In Server [message #487130 is a reply to message #487128]

Thu, 23 December 2010 01:03

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

Senior Member
Account Moderator

I don't understand it what you already did and what part is missing.

Regards
Michel

Report message to a moderator

Re: Storing XML File In Server [message #487138 is a reply to message #487130]

Thu, 23 December 2010 02:31

prasannamathan
Messages: 3
Registered: December 2010

Junior Member

Hi Michel,

Lets explain clearly,

Step 1:
My task is to copy xml file from my local machine to server specified location using oracle pl/sql. (Looking for help on this)

Step 2:
Then i wrote code to parse the xml file in that server specified location and store the data into the database using oracle pl/sql (this part is done)

Hope this will clear. Pls let me know if you need any further information.

Thanks in advance!

Report message to a moderator

Re: Storing XML File In Server [message #487148 is a reply to message #487138]

Thu, 23 December 2010 03:37

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

prasannamathan wrote on Thu, 23 December 2010 00:31

Step 1:
My task is to copy xml file from my local machine to server specified location using oracle pl/sql. (Looking for help on this)

You cannot do what you want to do the way that you want to do it. Oracle pl/sql can only read what is on the server, not on your local machine. You need to either use something outside of Oracle, such as operating system commands, or use the Oracle SQL*Loader utility which can read files on the local machine. To use SQL*Loader, you create a control file that tells it how to load what from where to where, create a table to load into, then run SQL*Loader either from the operating system command prompt or from SQL*Plus using the host command. Once you have it temporarily in the database, you can do whatever you want with it. If you like, you can spool it back out to a file on your server, then you can delete your table or you can just distribute it from the table. I have provided a demonstration of SQL*Loader below. You can load the file into either a clob column or xmltype column.

-- data file on local machine (test.xml):

<?xml version="1.0" encoding="ISO-8859-1" ?>
<EMPLOYEES>
<ItemGroupData ItemGroupOID="Emp1" ItemGroupRepeatKey="1">
<ItemData ItemOID="EMPNO" Value="11"/>
<ItemData ItemOID="ENAME" Value="aaa"/>
<ItemData ItemOID="JOB" Value="SE"/>
<ItemData ItemOID="MGR" Value="7902"/>
<ItemData ItemOID="HIREDATE" Value="20-OCT-80"/>
<ItemData ItemOID="SAL" Value="28000"/>
</ItemGroupData>
</EMPLOYEES>

-- SQL*Loader control file (test.ctl):

LOAD DATA
INFILE *
replace
INTO TABLE test_tab
FIELDS TERMINATED BY ","
(ext_fname  FILLER,
Data LOBFILE(ext_fname) terminated by eof
)
BEGINDATA:
test.xml

-- create table to load xml file into temporarily:

SCOTT@orcl_11gR2> create table test_tab
  2    (data  clob)
  3  /

Table created.

-- load the data using SQL*Loader from SQL*Plus:

SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log

-- results:

SCOTT@orcl_11gR2> select * from test_tab
  2  /

DATA
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1" ?>
<EMPLOYEES>
<ItemGroupData ItemGroupOID="Emp1" ItemGroupRepeatKey="1">
<ItemData ItemOID="EMPNO" Value="11"/>
<ItemData ItemOID="ENAME" Value="aaa"/>
<ItemData ItemOID="JOB" Value="SE"/>
<ItemData ItemOID="MGR" Value="7902"/>
<ItemData ItemOID="HIREDATE" Value="20-OCT-80"/>
<ItemData ItemOID="SAL" Value="28000"/>
</ItemGroupData>
</EMPLOYEES>


1 row selected.

SCOTT@orcl_11gR2>

Report message to a moderator

Previous Topic:	Improve the performance of XMLConcat
Next Topic:	Cannot find dispatch method

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 23:14:12 CST 2024