This is the VB version, providing you have MSXML 4.0 installed

'Create objects
Dim objADOCmd As New ADODB.Command
Dim objXMLDoc As New MSXML2.DOMDocument40

objADOCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=cislivepr01;Initial Catalog=ccpicisprod;UID=icisreader;Password=icisreader"

objADOCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

'The template query
objADOCmd.CommandText = "<Articles xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"<sql:query>" & _
"GetArticlesAsXML " & _
"</sql:query>" & _
"</Articles>"

'Output the XML stream into DOMDocument object
objADOCmd.Properties("Output Stream") = objXMLDoc

'Execute the command
objADOCmd.Execute , , adExecuteStream

objXMLDoc.save ("c:\test.xml")

This is the corresponding VBscript version, providing you have MSXML 4.0 installed

Dim objADOCmd
Dim objXMLDoc

'Create objects
Set objADOCmd = CreateObject("ADODB.Command")
Set objXMLDoc = CreateObject("MSXML2.DOMDocument.4.0")

objADOCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=cislivepr01;Initial Catalog=ccpicisprod;UID=icisreader;Password=icisreader"

objADOCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

'The template query
objADOCmd.CommandText = "<Articles xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & _
"<sql:query>" & _
"GetArticlesAsXML " & _
"</sql:query>" & _
"</Articles>"

'Output the XML stream into DOMDocument object
objADOCmd.Properties("Output Stream") = objXMLDoc

'Execute the command
objADOCmd.Execute , , adExecuteStream
objXMLDoc.save ("c:\test1.xml")


If you don't have MSXML 4.0 installed then this is possible, but don't forget to remove the CRLFs afterwards (see corresponding script)

exec sp_makewebtask @outputfile = 'D:\XML\myxmlfile.xml', 
@query = 'exec GetArticlesAsXML', 
@templatefile = 'D:\XML\template.tpl'