Returning XML Data as a Record Sets in SQL CLR

// ===========================================================================
// Returning XML Data as a Record Sets through the CLR in SQL Server 2005
//
// 1. Query the DB for the record.
// 2. Get the XML from the column.
// 3. Load the XML into a DataSet object.
// 4. Send the original record results of the row to the client.
// 5. Create record sets off of the DataSet using SqlDataRecord object.
// 6. Send each record back to the client.
//
// Uses: You could use this as an extensibility point in your application  by
// allowing users to add custom fields to the application at run time and then
// storing the application fields and field data in an XML file which is stored
// in an SQL Server column. Think of it as a database inside of a database. You
// could then use it to query the row/column to get the results into a readable
// form for Crystal or Reporting Services.
//
// http://www.donnfelker.com/returning-xml-data-as-a-record-sets-through-the-clr-in-sql-server-2005/
// ===========================================================================

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections.Generic;
using System.Data.Common;
using System.Xml;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TestProcedure(SqlInt32 recordId)
    {
        DataSet dataset = null;

        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            string xml = string.Empty;
            using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.CustomerTest WHERE Customer_Id = @recordId"))
            {
                command.Parameters.Add(new SqlParameter("@recordId", recordId));
                command.Connection = connection;

                connection.Open();

                using (DbDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        xml = reader["Extra_Info"].ToString();

                        // Load in the XML.
                        XmlReader xmlReader = new XmlTextReader(xml, XmlNodeType.Document, null);
                        dataset = new DataSet();
                        dataset.ReadXml(xmlReader);
                    }
                }

                SqlContext.Pipe.ExecuteAndSend(command);
            }
        }

        if (dataset != null)
        {
            foreach (DataTable table in dataset.Tables)
            {
                // Set up the record
                List<SqlMetaData> metaData = new List<SqlMetaData>();
                foreach (DataColumn column in table.Columns)
                {
                    metaData.Add(new SqlMetaData(column.ColumnName, SqlDbType.Variant));
                }
                SqlDataRecord record = new SqlDataRecord(metaData.ToArray());

                // Mark the begining of the result-set.
                SqlContext.Pipe.SendResultsStart(record);

                foreach (DataRow row in table.Rows)
                {
                    foreach (DataColumn column in table.Columns)
                    {
                        record.SetValue(table.Columns.IndexOf(column),
                            row[table.Columns.IndexOf(column)]);
                    }

                    // Send the row back
                    SqlContext.Pipe.SendResultsRow(record);
                }

                // Mark the end of the result-set.
                SqlContext.Pipe.SendResultsEnd();
            }
        }
    }

}