Return Nested XML from Heirarchical Query

From: Mark Petereit <Google_at_You-and-Improved.com>
Date: 17 Jul 2006 10:19:08 -0700
Message-ID: <1153156748.654937.275050@p79g2000cwp.googlegroups.com>

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> Received on Mon Jul 17 2006 - 12:19:08 CDT