| Need to insert/update data from xml into tables. [message #171565] |
Wed, 10 May 2006 07:15  |
mb_geeta
Messages: 13
Registered: May 2006
|
Junior Member |
|
|
Hi all,
I have an xml document from which i need to extraxt rows
and check each row with the table.If row exists,the record should be updated, else it should be inserted.Can any please suggest how to do this. This is very URGENT
|
|
|
|
|
|
|
|
| Re: Need to insert/update data from xml into tables. [message #172112 is a reply to message #172027] |
Mon, 15 May 2006 00:02  |
mb_geeta
Messages: 13
Registered: May 2006
|
Junior Member |
|
|
All that we need to do is open the file(using UTL_FILE),read it into CLOB and convert into XMLTYPE instance.Then the following code works:
for i in ( select EXTRACTVALUE(VALUE(t), '/EMP/EMPNO') empno,
EXTRACTVALUE(VALUE(t), '/EMP/ENAME') ename,
EXTRACTVALUE(VALUE(t), '/EMP/JOB') job,
EXTRACTVALUE(VALUE(t), '/EMP/MGR') mgr,
EXTRACTVALUE(VALUE(t), '/EMP/HIREDATE') hiredate,
EXTRACTVALUE(VALUE(t), '/EMP/SAL') sal,
EXTRACTVALUE(VALUE(t), '/EMP/COMM') comm
FROM TABLE(XMLSEQUENCE(EXTRACT(x, '/EMPLOYEES/EMP'))) t )
--where x is the XMLTYPE instance.
loop
Update EMP set ENAME=i.ename where EMPNO=i.empno;
if SQL%NOTFOUND then
insert into EMPNO values (i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
