Generating Multi Level Nodes In Stored Procedures

Hi all,

What I am trying to do is generate a stored procedure that is desired
to output XML in this type of format

<Parent Device>
<Device>
<Device ID>1</DeviceID>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device>

The area of concern is the child register, the XML being generated
disregards the Device the ChildRegister belongs to and always places it
as elements of the last device.


<Parent Device>
<Device>
<Device ID>1</DeviceID>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device>

I am trying to produce XML like the first one I described and have yet
to discover a way of associating the ChildRegister with the parent
Device in XML. I am not sure if it is a limitation of SQL Server, or if
my implementation is incorrect. If anyone could post hints or
solutions, I would greatly appreciate it.
A shortened version of the stored procedure is below


Cheers :)
Alvin


SELECT
1AS TAG
,NULL AS PARENT
,NULL AS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

FROM udetails INNER JOIN
Detail ON udetails.ID = Detail.ID
WHERE (uDetails.JobID = @ID)

UNION ALL

SELECT
2 AS TAG
,1 AS PARENT
,TempTable.DeviceIDAS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]
[color=blue]
>From #Temp as TempTable INNER JOIN[/color]
device ON TempTable.DeviceID = device.DeviceID

UNION ALL

SELECT
3 AS TAG
,2 AS PARENT
,NULL AS [Device!2!DeviceID!element]
,RegisterID AS [ChildRegister!3!RegisterID!element]

FROM #Temp t INNER JOIN
register ON t.DeviceID =
register.DeviceID

FOR XML EXPLICIT