Returning XML Data as a Record Sets through the CLR in SQL Server 2005

The Problem

I needed to write something like this …

SELECT * FROM CustomerTest WHERE Customer_Id = 1;

The data returned needed to return the row as well as the data inside of the XML Column in that row, in the same set. (The data in the XML column needed to be returned as sets of records as well.) The XML Contained was simple XML that looked like this:

(click for larger image)

The XML Column was called “Extra_Info”. This is where the above XML was stored.

Ok, lets get to the point… here’s what its supposed to look like when the query is run:

(click for larger image)

A little more about the problem domain..

Awhile back I had a client who wanted to store data in an XML Field, but the XML for each row could be extremely different in each row. One row might have a XML Document that has 1 set of data, while the next might have 10. These XML documents that were being stored did not share a common schema — not at all. But we needed to return the data in an SQL Query or SPROC that would allow the user to view the data as if it were tables in the database.

Solution

So how do we do that with one Query?

Easy – SQL CLR Integration.

We can create a CLR Stored Procedure to do this for us.
Here are the steps the CLR SPROC will go through to return the data.

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.

Code time baby…

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;
                if (connection.State != ConnectionState.Open)
                {
                    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();
            }
        }

    }

}

Code Review

The sproc is created with the code above.

If we find a record we load the xml into a variable and then load the DataSet with the XML using the ReadXml Method of the DataSet.

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);
}

Next, we loop through each data table and each record in the data table to create a SqlDataRecord. To build an SqlDataRecord we need a Record definition.These record definitions are defined as SqlMetaData objects. Therefore we need populate an array of SqlMetaData objects by looping through the columns to get the column information. At this point we’re building the columns in which the data will exist in.

// 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());

From here, we will build the actual records. This is done by the following method:

• Inform the context that we’re going to begin sending a record by using the SendResultsStart method of the Pipe class.
• When a record is built, we want to send it to the client by calling the SendResultsRow method. This sends the row to the client. We will do this for each record built.
• After the records have been sent we want to tell the client we’re done sending records for this set. We do this by calling the SendResultsEnd method.

This happens for each DataTable in the DataSet. And then finally the results are sent back to the client.

Resolution

Once deployed, the sproc (which I’ve named “TestProcedure”) will show up under the StoredProcedures area of SQL Server, as shown here:

And now you have a stored procedure that will return results of the row as well as the data that is in the XML column, all in the same result set.

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.