How to Export from Sql Server 2005 to a well-formed XML File

Its a shame I have to write this one. It started out innocent enough. I need some data dumped out of a query and into a well-formatted xml file for further xslt transformations.

Piece of cake, just run a "FOR XML" query on a SQL server, output the results to file, and you will have an easy-to-translate XML file, right? Not exactly...

Say this is Northwind and I want the categories joined with the products:

DECLARE @output xml;
SET     @output =
(
SELECT
categories.categoryid AS '@categoryID',
 (
SELECT  
products.productid AS '@productID'
FROM       
Products
WHERE   
products.categoryid = categories.categoryid
FOR xml path('product'), type, root('products')
)
FROM       
Categories
FOR xml path('category'), TYPE, root('categories')
);

SELECT     @output;

Notice that I need something more powerful than RAW or AUTO mode, I want to define my own custom xml schema using the PATH syntax.
Then, I can see in the sql output window something like this:

<categories>
<category categoryID="1">
 <products>
   <product productID="1" />
   <product productID="2" />
   <product productID="24" />
   <product productID="34" />
   <product productID="35" />
   <product productID="38" />
   <product productID="39" />
   <product productID="43" />
   <product productID="67" />
   <product productID="70" />
   <product productID="75" />
   <product productID="76" />
 </products>
</category>
<category categoryID="2">
 <products>
   <product productID="3" />
   <product productID="4" />
   <product productID="5" />
   <product productID="6" />
   <product productID="8" />
   <product productID="15" />
   <product productID="44" />
   <product productID="61" />
   <product productID="63" />
   <product productID="65" />
   <product productID="66" />
   <product productID="77" />
 </products>
</category>
<category categoryID="3">
 <products>
   <product productID="16" />
   <product productID="19" />

..you get the idea. Now I have the format and results I want, but this needs to programmatically be exported to a file. it time to switch my output from grid/text to a file... only to get this garbage in my file:

------------------------------------------------------------------------
<categories><category categoryID="1"><products><product productID="1" /><product productID="2" /><product productID="24" /><product productID="34" /><product productID="35" /><product productID="38" /><product productID="39" /><product productID="43" /><product productID="67" /><product productID="70" /><product productID="75" /><product productID="76" /></products></category><category categoryID="2"><products><product productID="3" /><product productID="4" /><product productID="5" /><product productID="6" /><product productID="8" /><product productID="15" /><product productID="44" /><product productID="61" /><product productID="63" /><product productID="65" /><prod
(1 row(s) affected)

And that is the result of a sql query for xml exported to a file. Obviously, we can't pass this to an xml parser and expect anything to work. What to do? I spent an extra 1/2 hour and ended up making a little ADO.NET program that would:

1. Open a sql query file
2. Connect to a sql server database
3. Execute the query, read the full result set as an xml stream, and save a well-formatted xml document at the specified destination.
   

Here is the usage:

XmlExtract.exe [sql query file name] [connection string] [output file destination]
For example:
XmlExtract.exe query.sql "data source=pweissbrod0308\\sql2k5;integrated security=true;initial catalog=northwind;" "C:\output.xml"


And finally, here is the source code for my program:

using System;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Xml;

namespace XmlExtract
{
  public class Program
  {
      private static string connectionString;

      private static string outputFileName;
      private static string sqlQueryFilename;

      private static void Main(string[] args)
      {
          sqlQueryFilename = args[0];
          connectionString = args[1];
          outputFileName = args[2];

          TextReader textReader = new StreamReader(sqlQueryFilename);
          string query = textReader.ReadToEnd();
          textReader.Close();
          Console.WriteLine(query);
          var stringBuilder = new StringBuilder();

          SqlDataReader sqlDataReader;
          using (var sqlConnection = new SqlConnection(connectionString))
          {
              var sqlCommand = new SqlCommand(query, sqlConnection);

              sqlConnection.Open();
              sqlDataReader = sqlCommand.ExecuteReader();

              while (sqlDataReader.Read())
              {
                  stringBuilder.Append(sqlDataReader.GetString(0));
              }
              sqlDataReader.Close();

              var xmlDocument = new XmlDocument();
              xmlDocument.LoadXml(stringBuilder.ToString());

              if (File.Exists(outputFileName))
                  File.Delete(outputFileName);

              var xmlTextWriter = new XmlTextWriter(outputFileName, Encoding.UTF8);
              xmlTextWriter.Formatting = Formatting.Indented;
              xmlTextWriter.WriteStartDocument();
              xmlDocument.WriteTo(xmlTextWriter);
              xmlTextWriter.Flush();
              xmlTextWriter.Close();
          }
      }
  }
}

I hope this helps anyone with similar endeavors, feel free to paste this into snippet compiler to get a quick app up and running, or patch it into your current project for something more custom.