SQL & PL/SQL

Oracle 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