OraFAQ Forum: JDeveloper, Java & XML

Home » Developer & Programmer » JDeveloper, Java & XML » PARSE XML (Oracle 10 g ,windows)

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

PARSE XML [message #343161]

Tue, 26 August 2008 08:51

manickam
Messages: 9
Registered: June 2005
Location: chennai

Junior Member

Hi all,

We are in the process of migrating the database from sql server to oracle.

We face certain blocks while converting the stored procedure from tsql(sqlserver) to plsql(oracle).

sql server, in a procedure we accept the input as a XML and parse it and split it and then put those data inside a table.

Now we are supposed to do the same in PLSQL, so help me out to achieve it. below i given an example , so what is the proper query to acheive it.

Let us consider our destination table as

create table project (projid int,empid int,department int,sal int)

The input XML is

<Project ProjID="P002">
<Employee EmplID="1001">
<Department>10</Department>
<Sal>1000</Sal>
</Employee>
<Employee EmplID="1002">
<Department>20</Department>
<Sal>2000</Sal>
</Employee>
</Project>

after inserting this xml the project table should hold

projid empid department sal
002 1001 10 1000
002 1002 20 2000

WHAT IS THE QUERY?

Report message to a moderator