Re: Parent-Child Hierarchy to XML

But I am using the DBMS_XMLGEN package to convert my results to XML. The results come back as a "flat" format, not a parent-child format.

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

> 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.
>

You should find some at:
http://www.psoug.org/reference/dbms_xmlgen.html

-- 
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)