Retriving data as an XML from SQL Server

With SQL Server we can generate XML output using different methods. Using TSQL keyword FOR XML along with AUTO, RAW, PATH and EXPLICIT we could generate almost any XML structure that we might need.
Following sample is using XML Explicit option for retriving data.

SELECT 1 AS Tag,



NULL AS Parent,



NULL AS 'root!1!',



NULL AS 'Schedule!2!ID!Element',



NULL AS 'Schedule!2!ScheduledDate!Element',



NULL AS 'Schedule!2!ResourceID!Element'



UNION



SELECT 2 AS Tag, 1 AS Parent,



null,RS.ID, RS.ScheduledDate,RS.ResourceID from dbo.tblTest



For

XML EXPLICIT





Sample Result generated will be in this format:

<root>

<Schedule> <ID>69</ID> <

<ScheduledDate>2011-05-08T00:00:00</ScheduledDate> <ResourceID>2</ResourceID>

</Schedule>



<Schedule><ID>70</ID><

<ScheduledDate>2011-07-08T00:00:00</ScheduledDate><ResourceID>23</ResourceID>

</Schedule>

</root>