With all the samples I provided earlier I've been giving a simple query to be output into XML, but in a real life situation the xml files are much more complex than this, perhaps you will have to deal with N:N relationships.

Let's suppose the following database schema, that reflects a CD collection, I've simplified the entities and attributes available to make it a simpler example:

Recording (RecordingID,RecordingTitle)

RecAut (RecordingID,AuthorID)

Author (AuthorID,AuthorName)

This database schema associate this entities like this (each record can have several authors and each author can have several records). Sure that this schema of the database is the reflection of one real-life problem you want to address and this schema could be simpler or more complex depending on the problem you want to address.

The problem posed here is how are we going to output this in XML?

If we use a regular join for the tables

SELECT r.RecordingTitle,a.AuthorName

FROM RecAut ra, Author a, Recording r

WHERE ra.AuthorID = a.AuthorID

AND ra.RecordingID = r.RecordingID

This will produce a flatten xml file

<?xml version = '1.0'?>

<ROWSET>

<ROW num=1>

<RECORDINGTITLE>Caetano &amp; Bethania ao Vivo</RECORDINGTITLE>

<AUTHORNAME>Caetano</AUTHORNAME>

</ROW>

<ROW num=2>

<RECORDINGTITLE>Caetano &amp; Bethania ao Vivo</RECORDINGTITLE>

<AUTHORNAME>Bethania</AUTHORNAME>

</ROW>

</ROWSET>

In order to obtain a nested xml file, you need to use subqueries, something like this

SELECT r.RECORDINGTITLE,

CURSOR(SELECT a.AUTHORNAME

FROM recaut ra, author a

WHERE ra.AUTHORID = a.AUTHORID

AND ra.RecordingID = r.RecordingID) Authors

FROM recordings r

This will produce the following xml:

<?xml version = '1.0'?>

<ROWSET>

<ROW num=1>

<RECORDINGTITLE>Caetano &amp; Bethania ao Vivo</RECORDINGTITLE>

<AUTHORS>

<AUTHORS_ROW num=1>

<AUTHORNAME>Caetano</AUTHORNAME>

</AUTHORS_ROW>

<AUTHORS_ROW num=2>

<AUTHORNAME>Bethania</AUTHORNAME>

</AUTHORS_ROW>

</AUTHORS>

</ROW>

</ROWSET>

Where is the complete PL/SQL procedure:

CREATE OR REPLACE procedure NestedResults is

queryCtx DBMS_XMLquery.ctxType;

result CLOB;

strSQL VARCHAR2(300);

begin

strSQL := 'SELECT r.RECORDINGTITLE,

CURSOR(SELECT a.AUTHORNAME

FROM recaut ra, author a

WHERE ra.AUTHORID = a.AUTHORID

AND ra.RecordingID = r.RecordingID) Authors

FROM recordings r';

queryCtx := DBMS_XMLQuery.newContext(strSQL);

result := DBMS_XMLQuery.getXML(queryCtx);

DBMS_XMLQuery.closeContext(queryCtx);

Clob2Table(result);

end;

Final Note: One other possibility of obtaining nested xml files is using the object/relational capabilities of Oracle 8i, I've been studying some of this capabilities to bring you one snippet that shows you how to accomplish this. But this requires a change in the database schema, in already implemented database the use of subqueries may perhaps be the best solution.