title: Output SQL Server table to XML in a directory on the server
body: <p>I'm trying to export my </p>
  
  <pre><code>select column1, column2 
  from table1 
  </code></pre>
  
  <p>into a XML file and save it into a directory of <code>c:\Test\</code> on the server. </p>
  
  <p>I would also like the file to be overwritten when the i run the query again with same output xml name.</p>
  
  <p>Is there away of doing this without going through creating a how SSIS Job. I have used the feature <code>FOR XML AUTO</code> and <code>FOR XML PATH</code> but these do not give you a formed XML that will open as well as the option to save into a location. </p>
  
  <p>Any ideas on how to do this would be good! </p>
  
  <p>I have run the following after Aruns Comment :</p>
  
  <p>I have run the following : </p>
  
  <pre><code>DECLARE @fileName VARCHAR(50) DECLARE @sqlStr VARCHAR(1000) DECLARE @sqlCmd VARCHAR(1000) 
  SET @fileName = 'C:\test.xml'  SET @sqlStr = 'select TOP 50 [Key_Scores],    [Total_Scores] from Futures.[dbo].[Aco_Scores] FOR XML PATH (''Key_Scores''),    ROOT (''Key_Scores.Total_Scores'')'
  SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T' EXEC xp_cmdshell @sqlCmd
  </code></pre>
  
  <p>Table is called Aco_Scores and the 2 columns are called Key_Scores and Total_Scores. </p>
  
  <p>Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'Futures.dbo.Aco_Scores'.</p>
  
  <p>Error = [Microsoft][SQL Server Native Client 11.0]Unable to resolve column level collations</p>
  
  <p>I've entered my columns wrong?</p>
  
  <p>My New Query i have tried is :</p>
  
  <p>This query produces an non opening XML file, Actually i have tried the PATH and AUTO but no joy. -- URL Summary Rotary</p>
  
  <pre><code>  DECLARE @fileName VARCHAR(50) DECLARE @sqlStr VARCHAR(1000) DECLARE @sqlCmd VARCHAR(1000) 
    SET @fileName = 'C:\XML\Summary_Rotary.XML'  SET @sqlStr = 'select TOP 8 [Top_8_Hits], [Best_Score], [Lowest_Score], [Average_Score], [Final_Rating] from Futures.[dbo].[Summary_Rotary] FOR XML AUTO' 
   SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T'  EXEC xp_cmdshell @sqlCmd
  </code></pre>
  
  <p>Which produces a non working XML</p>