Design a procedure to fetch data from table and output XML in .NET
610795Jan 7 2010 — edited Jan 8 2010Oracle 9i
How can i design a procedure to fetch data from a table and the output should be in XML in the calling environment.
create table emp(empno number PRIMARY KEY, ename varchar2(40), sal number)
insert into table emp values(1, 'Dave',1000);
insert into table emp values(2, 'Den',2000);
insert into table emp values(3, 'Ben',1000);
insert into table emp values(4, 'Bel',1000);
Is this idea correct
Create Package pack
is
type reft is REF CURSOR return SYS.XMLTYPE;
end pack;
create procedure proc(eid in number, cs out pack.reft)
is
begin
open cs for select ename, sal
from emp
where empno=eid;
end;
calling environment example asp.net or c#.net then HOW TO WRITE THE BELOW
declare
refvar pack.reft;
xmldt SYS.XMLTYPE; -- unsure of this step, i am assuming that a variable of xml data type relating to the calling environment should be declared.
begin
proc(&eid,refvar);
loop
forall i in 1 .. refvar%rowcount;
exit when refvar%notfound;
fetch refvar bulkcollect into xmldt
-- display all the data example like <empid> 10 <ename> 'Dave' </ename> --<sal>1000</sal></empno><empid> 20 <ename> 'Den' </ename> <sal>2000</sal></empno>
end loop;
close refvar;
end;
Drop table emp cascade;
If this is entirely wrong please, show a proper solution where you will be extracting data from tables and supplying it to a .NET environment as a XML file with XML tags.
Thank you.
Edited by: Trooper on Jan 8, 2010 12:15 AM