Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Parent-Child Hierarchy to XML

Re: Parent-Child Hierarchy to XML

From: Leo J. Hart IV <leo.hart_at_nospam.fmr.com>
Date: Wed, 24 Mar 2004 10:26:10 -0500
Message-ID: <XMh8c.467$Mn.846@news-srv1.fmr.com>


Thanks for your reply!

Would you mind giving me an example? I'm not quite following...

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1080076911.375856_at_yasure...
Leo J. Hart IV wrote:

> 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

One way would be to use CONNECT BY and then wrap that, as an inline view into the XMLGEN query.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Wed Mar 24 2004 - 09:26:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US