Parent-Child Hierarchy to XML

Hello,

Hopefully this will be an easy problem to solve as it seems like something many people would want to do. I have a parent-child relationship in an Oracle 9i table:

	CREATE TABLE tst_prnt_chld


		(ID		NUMBER NOT NULL,

		label		VARCHAR2(50) NOT NULL,
		prnt_id 	NUMBER)
	/

This table is populated with the following data:

	id	name	parent_id
	--------	--------	-----------
	1	One		
	2	Two		
	3	Three	2
	4	Four	2
	5	Five	3

using the following DML:

	INSERT INTO tst_prnt_chld


		(id, label, prnt_id)

	VALUES


		(1, 'One', NULL)

	/
	INSERT INTO tst_prnt_chld


		(id, label, prnt_id)

	VALUES


		(2, 'Two', NULL)

	/
	INSERT INTO tst_prnt_chld


		(id, label, prnt_id)

	VALUES


		(3, 'Three', 2)

	/
	INSERT INTO tst_prnt_chld


		(id, label, prnt_id)

	VALUES


		(4, 'Four', 2)

	/
	INSERT INTO tst_prnt_chld


		(id, label, prnt_id)

	VALUES


		(5, 'Five', 3)

	/

I can perform a "CONNECT BY" query:

	SELECT	   LPAD(' ', LEVEL * 2) || label
	FROM	   tst_prnt_chld
	START WITH prnt_id IS NULL
	CONNECT BY PRIOR ID = prnt_id

 to get a plain-text hierarchy tree:

	  One
	  Two
	    Three
	      Five
	    Four

My question is: how, using the Oracle XML DB features, can I output this parent-child relationship as an XML tree:

	<node name="One"></node>
	<node name="Two>
		<node name="Three">
			<node name="Five"></node>
		</node>
		<node name="Four"></node>
	</node>
		

Note: the formatting doesn't matter. I just want a well-formed XML document.

I tried using the DBMS_XMLGEN package to generate these results:

	DECLARE
		lSql VARCHAR2(4000);
		TYPE RefCursor IS REF CURSOR;
		lCsr RefCursor;
		lXml CLOB;
	BEGIN
		lSql := '
			SELECT	   LPAD('' '', LEVEL * 2) || label as node
			FROM	   tst_prnt_chld
			START WITH prnt_id IS NULL
			CONNECT BY PRIOR ID = prnt_id';
			
		
		lXml := dbms_xmlgen.getxml(lSql);
		
		PRINT_CLOB(lXml);
	END;
	/

But this is what it came up with (which does make total sense):

	<ROWSET>
		<ROW>
			<NODE>One</NODE>
		</ROW>
		<ROW>
			<NODE>Two</NODE>
		</ROW>
		<ROW>
			<NODE>Three</NODE>
		</ROW>
		<ROW>
			<NODE>Five</NODE>
		</ROW>
		<ROW>
			<NODE>Four</NODE>
		</ROW>
	</ROWSET>
		

Any idea as to how I can get the results I want? Note: I can also use Oracle's XML Java SDKs if they will be of any use.

Thanks,
Leo Hart Received on Tue Mar 23 2004 - 09:25:16 CST