dbms_xmlgen

dbms_xmlgen.newContextFromHierarchy(queryString IN VARCHAR2)
RETURN ctxHandle;

set serveroutput on

DECLARE
qryctx dbmx_xmlgen.ctxhandle;
result XMLTYPE;

PROCEDURE lob_output (p_clob CLOB) IS l_clob CLOB;
l_clob_length NUMBER;
l_iterations NUMBER;
l_chunk VARCHAR2(32767);
l_chunk_length NUMBER := 32767;
BEGIN
l_clob := p_clob;
l_clob_length := dbms_lob.getlength(l_clob);
l_iterations := CEIL(l_clob_length / l_chunk_length);

FOR i IN 0 .. l_iterations - 1 LOOP
l_chunk := dbms_lob.substr(l_clob,l_chunk_length,i*l_chunk_length+1);
dbms_output.put_line(l_chunk);
END LOOP;
END;
BEGIN
qryctx := dbms_xmlgen.newcontextFromHierarchy('SELECT level,
XMLElement("Position", XMLElement("Name", ename), XMLElement("Title",
job)) FROM emp CONNECT BY PRIOR empno = mgr START WITH mgr is NULL');

result := dbms_xmlgen.getxmltype(qryctx);

dbms_xmlgen.closeContext(qryctx);

lob_output(RESULT.getClobVal());
END;
/