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;
