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]
>From #Temp as TempTable INNER JOIN
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(teohster@.gmail.com) writes:
> 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
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.public.sqlserver.xml though.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
