sql server - How to dump data from MSSQL to XML with SQL Agent? -

i need export data sql procedure mssql (2012) xml , run script periodically sql server agent.

i directly sql server, because doing console application generating xml structure raw data sql , takes on 10 hours! output xml has 300m.

does how it? have stored procedure generating xml output.

thanks much.

the procedure executed successfully. had set rights destination folder user "sqlserveragent", because agent finishes wrote nothing.

but have small problem final xml. @ beginning , @ end of xml file included information agent.

do know how remove header , footer?

header:

job 'xml dump' : step 1, 'xml dump procedure' : began executing 2013-07-23 12:24:52  xml_f52e2b61-18a1-11d1-b105-00805f49916b --------------------------------------------------------------------------------- 

footer:

(146270 rows(s) affected) 

---

stored procedure contains this:

create procedure getxmldumpcompletedata (@xml xml)     set @xml = (select * [mytable]          xml auto, elements, root('data'))     select @xml 

command in job agent:

declare @xml xml  exec    [dbo].[getxmldumpcompletedata]         @xml = @xml output  select  @xml n'@xml'  go