Use this to get the sql server query output into an XML file.
DECLARE @SQL VARCHAR(2000)
DECLARE @ExportResult INT
SET @SQL = 'bcp "SELECT * from [DBName].dbo.TableName FOR XML PATH (''OwnerStatus''), ROOT(''Root'')" queryout D:\EMS\Data.XML -c -t, -T -S SERVERNAME'
EXEC master..xp_cmdshell @SQL
EXEC @ExportResult = master..xp_cmdshell @SQL ,NO_OUTPUT
SELECT @ExportResult
The command requires that cmdshell be enabled. In sql server 2005 this is found in the surface area configuration. In 2008 this can be enabled in the SSMS by running the following.
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
GO
RECONFIGURE
