How to output a file from a stored procedures in MS SQL, this is how I did it a few years ago

exec master..xp_cmdShell ‘bcp “SELECT  url.loc, url.publication_date, url.keywords  FROM ICONews..vw_googlenews url For XML Auto,Elements”  queryout  C:InetpubwwwrootwebsiteSQLgoogle.xml -c -t, -T -S IP address’

Link:

http://www.sqlteam.com/article/exporting-data-programatically-with-bcp-and-xp_cmdshell