How to Export Query as XML - SQL Server 2008

Save Recordset as XML using Select Query in SQL Server 2008

Let us consider the a table (shown below) with couple of columns that needs to be exported as XML

The following query

SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML PATH

Is used for generating a XML 

 On the other hand, If you want to have a meaningfully named element instead of , you need to specify the same in the query. The following query has the row element as well as the root element


SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML path ('TrainInfo'), root ('ParentInfo')