Search This Blog

Wednesday, January 10, 2007

Getting the Hierarchial data in XML format

set serveroutput on
set long 200000
set pagesize 20000
drop table sqlx_display;
create table sqlx_display(id number, xmldoc XMLType);

-- Getting the data as attributes

declare
qryctx dbms_xmlgen.ctxhandle;
result XMLType;
begin
qryctx := dbms_xmlgen.newcontextFromHierarchy(
'select level, xmlelement("emp", XMLATTRIBUTES ( e.empno as "ID", e.ename AS "name"))
from scott.emp e
start with ename=''KING'' connect by prior empno=mgr
order siblings by hiredate');
result := dbms_xmlgen.getxmltype(qryctx);
dbms_output.put_line('');
dbms_output.put_line(to_char(dbms_xmlgen.getNumRowsProcessed(qryctx)));
dbms_output.put_line('
');
insert into sqlx_display values (2, result);
commit;
dbms_xmlgen.closecontext(qryctx);
end;

select xmldoc from sqlx_display where id = 2;

-- Getting the data with all as elements

declare
qryctx dbms_xmlgen.ctxhandle;
result XMLType;
begin
qryctx := dbms_xmlgen.newcontextFromHierarchy(
'select level, xmlelement("emp", xmlelement("enumber", empno),
xmlelement("name", ename),
xmlelement("Salary", sal),
xmlelement("Hiredate", hiredate)) from scott.emp
start with ename=''KING'' connect by prior empno=mgr
order siblings by hiredate');
result := dbms_xmlgen.getxmltype(qryctx);
dbms_output.put_line('');
dbms_output.put_line(to_char(dbms_xmlgen.getNumRowsProcessed(qryctx)));
dbms_output.put_line('
');
insert into sqlx_display values (2, result);
commit;
dbms_xmlgen.closecontext(qryctx);
end;

select xmldoc from sqlx_display where id = 2;