Re: Return Nested XML from Heirarchical Query

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Mon, 17 Jul 2006 20:10:47 +0200
Message-ID: <e9gjr3$8d0$00$1@news.t-online.com>

Mark Petereit schrieb:
> 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>
>

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/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1610

Best regards

Maxim Received on Mon Jul 17 2006 - 13:10:47 CDT