Return Nested XML from Heirarchical Query

Mark Petereit · *nix forums beginner Joined: 17 Jul 2006 Posts: 1

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>

Maxim Demenko · *nix forums Guru Joined: 24 Apr 2005 Posts: 428

Mark Petereit schrieb:

Google