I have the following hierarchical query that returns our company's
organizational structure:


SELECT TRIM(E.LAST_NAME)||', '||TRIM(E.NICK_NAME) AS Name,
J.DESCRIPTION AS Title
FROM LAWPRODHR.EMPLOYEE E
JOIN LAWPRODHR.JOBCODE J ON
J.COMPANY = E.COMPANY AND
J.JOB_CODE = E.JOB_CODE
LEFT JOIN LAWPRODHR.HRSUPER S ON
S.COMPANY = E.COMPANY AND
S.EMPLOYEE = E.EMPLOYEE
CONNECT BY PRIOR S.CODE = E.SUPERVISOR


How would I utilize XMLAgg, XMLElement, XMLForrest, etc. to generate
nested XML elements that properly reflect the organizational structure?


For example, the executive branch of the U.S. Government would look
like this:


<Position>
<Name>George Bush</Name>
<Title>President</Title>
<Position>
<Name>Donald Rumsfeld</Name>
<Title>Secretary of Defense</Title>
<Position>
<Name>Gordon England</Name>
<Title>Deputy Secretary of Defense</Title>
</Position>
</Position>
<Position>
<Name>Condoleezza Rice</Name>
<Title>Secretary of Status</Title>
<Position>
<Name>Karen Hughes</Name>
<Title>Under Secretary for Public Diplomacy and Public
Affairs</Title>
</Position>
</Position>
</Position>

Mark Petereit schrieb:
If you are on 10g, there is an out the box solution , you can try
something like this ( i took the standard emp table only to test this
block, you can replace the relevant part with your query).

DECLARE
qryctx DBMS_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;
/

Btw, example is almost copy/pasted from
http://download-east.oracle.com/docs...htm#sthref1610

Best regards

Maxim