How to store a sql table data into an XML file?

I have a开发者_StackOverflow社区 situation where I have a data table in SQL server Database. Now i want it to be inserted into an XML File through SQL Commands. How can I do it?

You can execute query from cmd shell with -o parameter or the same command by xp_cmdshell. in select statement you can use for xml option. and results must be printed with print command.
for example:

SQLcmd -S "(local)\sqlexpress" -E -d "EFEx" 
-q "declare @i nvarchar(max) 
set @i = (select * from [Group] for xml auto,root(''groups'')) 
print @i" 
-o "C:\Projs\results.xml"

don't forget security rights for creating file and enable xp_cmdshell

exec sp_configure 'xp_cmdshell', 1
reconfigure;

I hope this helps.

The short answer is no you can't output to a file using pure T-SQL. Whatever client you are using to connect to the SQL server needs to pipe the output to a file.

You have a few choices: - BCP utility (recommended, read the XML format file section) - SQLCMD utility - Build an SSIS package to do the job (sounds like overkill for your case)

That said you can execute these commands from a SQL session using xp_cmdshell.