Generating Multi Level nodes in Stored Procedures

[teohster@gmail.com]

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>
<ChildRegiste r>
<ChildRegisterI D>22</ChildRegisterID >
</ChildRegister>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegiste r>
<ChildRegisterI D>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>
<ChildRegiste r>
<ChildRegisterI D>23</ChildRegisterID >
</ChildRegister>
<ChildRegiste r>
<ChildRegisterI D>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
1 AS TAG
,NULL AS PARENT
,NULL AS [Device!2!Device ID!element]
,NULL AS [ChildRegister!3 !RegisterID!ele ment]

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

UNION ALL

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

UNION ALL

SELECT
3 AS TAG
,2 AS PARENT
,NULL AS [Device!2!Device ID!element]
,RegisterID AS [ChildRegister!3 !RegisterID!ele ment]

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

FOR XML EXPLICIT

[Erland Sommarskog]

Re: Generating Multi Level nodes in Stored Procedures

(teohster@gmail .com) writes:[color=blue]
> 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[/color]

For all problems like this, it is a good idea to post:

o CREATE TABLE statements of the tables inolved.
o INSERT statements with sample data.
o The desired output given the sample data.

You posted the last, but not the first two.

This permits people to post a tested solution to your query. In this
case, an aggrevating factor is that I am not extremely versed in XML,
so I would have to play around with the query.

It may be more effective to ask the real pros in
microsoft.publi c.sqlserver.xml though.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp