Hello. Essentially, I'm trying to find a way to run a stored procedure in SQL Server 2005 once per day. What it will do is generate specific XML and need to (hopefully) export that to a file (one file per day, if data exists). I've gotten the stored procedure working exactly as I want it to and it's currently saving the correctly formatted XML into a table with an xml data type. Thus if I just run a SELECT query in Management Studio on that column, the results are a link which once I click on opens a new tab with the properly formatted XML, just as I want.
The problem is, how do I get this out into an xml file on the server somewhere automatically, either using the same stored procedure or some other method? I've tried testing the bcp.exe option running something like:
bcp "SELECT theXML FROM DB.dbo.Table FOR XML RAW" queryout "C:\temp\sql\Test1.xml" -S"server" -U"user" -P"pass" -c -r -t
But the resulting xml file not only adds two new 'root' sections (which is unnecessary as the XML in the database is already exactly how I need it), but it's all on a single line, instead of properly formatted XML. Am I missing something, is there an easier way, or what? Thanks for your help!
The problem is, how do I get this out into an xml file on the server somewhere automatically, either using the same stored procedure or some other method? I've tried testing the bcp.exe option running something like:
bcp "SELECT theXML FROM DB.dbo.Table FOR XML RAW" queryout "C:\temp\sql\Test1.xml" -S"server" -U"user" -P"pass" -c -r -t
But the resulting xml file not only adds two new 'root' sections (which is unnecessary as the XML in the database is already exactly how I need it), but it's all on a single line, instead of properly formatted XML. Am I missing something, is there an easier way, or what? Thanks for your help!
