Execute Procedure for XML Auto

Introduction

For several years now, SQL Server has had the ability to convert the results of an arbitrary SELECT statement into XML by appending the FOR XML AUTO clause. For example, whilst:

  SELECT TOP 10 * FROM sys.types

generates a standard rowset:

name	system_type_id	user_type_id	schema_id	principal_id	max_length	precision	scale	collation_name	is_nullable	is_user_defined	is_assembly_type	default_object_id	rule_object_id	is_table_type
image	34	34	4	NULL	16	0	0	NULL	1	0	0	0	0	0
text	35	35	4	NULL	16	0	0	Latin1_General_CI_AS	1	0	0	0	0	0
uniqueidentifier	36	36	4	NULL	16	0	0	NULL	1	0	0	0	0	0
date	40	40	4	NULL	3	10	0	NULL	1	0	0	0	0	0
time	41	41	4	NULL	5	16	7	NULL	1	0	0	0	0	0
datetime2	42	42	4	NULL	8	27	7	NULL	1	0	0	0	0	0
datetimeoffset	43	43	4	NULL	10	34	7	NULL	1	0	0	0	0	0
tinyint	48	48	4	NULL	1	3	0	NULL	1	0	0	0	0	0
smallint	52	52	4	NULL	2	5	0	NULL	1	0	0	0	0	0
int	56	56	4	NULL	4	10	0	NULL	1	0	0	0	0	0

simply appending a FOR XML clause (in this case FOR XML AUTO):

  SELECT TOP 10 * FROM sys.types FOR XML AUTO

generates an XML fragment instead:

XML_F52E2B61-18A1-11d1-B105-00805F49916B

<sys.types name="image" system_type_id="34" user_type_id="34" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="text" system_type_id="35" user_type_id="35" schema_id="4" max_length="16" precision="0" scale="0" collation_name="Latin1_General_CI_AS" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="uniqueidentifier" system_type_id="36" user_type_id="36" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="date" system_type_id="40" user_type_id="40" schema_id="4" max_length="3" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="time" system_type_id="41" user_type_id="41" schema_id="4" max_length="5" precision="16" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="datetime2" system_type_id="42" user_type_id="42" schema_id="4" max_length="8" precision="27" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="datetimeoffset" system_type_id="43" user_type_id="43" schema_id="4" max_length="10" precision="34" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="tinyint" system_type_id="48" user_type_id="48" schema_id="4" max_length="1" precision="3" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0"/><sys.types name="smallint" system_type_id="52" user_type_id="52" schema_id="4" max_length="2" precision="5"

Clicking on the hyperlink shows you the full XML fragment in all its glory:

<sys.types name="image" system_type_id="34" user_type_id="34" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="text" system_type_id="35" user_type_id="35" schema_id="4" max_length="16" precision="0" scale="0" collation_name="Latin1_General_CI_AS" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="uniqueidentifier" system_type_id="36" user_type_id="36" schema_id="4" max_length="16" precision="0" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="date" system_type_id="40" user_type_id="40" schema_id="4" max_length="3" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="time" system_type_id="41" user_type_id="41" schema_id="4" max_length="5" precision="16" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="datetime2" system_type_id="42" user_type_id="42" schema_id="4" max_length="8" precision="27" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="datetimeoffset" system_type_id="43" user_type_id="43" schema_id="4" max_length="10" precision="34" scale="7" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="tinyint" system_type_id="48" user_type_id="48" schema_id="4" max_length="1" precision="3" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="smallint" system_type_id="52" user_type_id="52" schema_id="4" max_length="2" precision="5" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />
<sys.types name="int" system_type_id="56" user_type_id="56" schema_id="4" max_length="4" precision="10" scale="0" is_nullable="1" is_user_defined="0" is_assembly_type="0" default_object_id="0" rule_object_id="0" is_table_type="0" />

Note that I've referred to this as an XML fragment as it contains no root element. We can add one if we like by modifying the FOR XML AUTO clause, but I want to keep this as vanilla as possible.

Now comes the tricky part - image the data you want to return as XML isn't available directly from a table or view, but is selected by a stored procedure. How you you select that that data as XML?

Well, one option is simply to update the stored procedure such that it selects XML by adding a FOR XML clause within the procedure itself. But that'll break existing callers of the stored procedure.

You could copy/paste the stored procedure into a new one, and alter the new one to select XML. But that creates a maintenance burden as every time one stored procedure it changed, someone needs to remember to change the other one too to keep them in sync.

You could wrap one stored procedure in another. So your new stored procedures creates a temporary table, or perhaps a table variable, and does a INSERT INTO ... EXEC to populate the table with the results of the actual stored procedure, then selects the results from the temporary table with a FOR XML clause. For a lot of people, this is the option that'll work best. But it relies on the 'outer' stored procedure knowing the exact structure of the rowset selected by the 'inner' stored procedure, which may change at some point in the future. It's a solution which only works for the one stored procedure it's been coded for.

Isn't there a more flexible, generic solution? Yes there is...

Managed Stored Procedures

Since SQL Server 2005 we've been able to create stored procedures in managed code (i.e. in .NET) rather than just in Transact-SQL. By creating the 'outer' stored procedure mentioned above in managed code, we can have it operate in an entirely generic manner. This means we can call any existing stored procedure, without modifying it, and have the results returned to us as XML. Here's how:

1. In Visual Studio (I'm using 2010, but 2008 or even 2005 should be fine) create a new Class Library project (I called mine SqlServerClr) and re-name the default Class1.cs to StoredProcedures.cs.

2. Use Project Properties to ensure the Target Framework is .NET Framework 2.0.

3. Replace the contents of the StoredProcedures class with:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

namespace SqlServerClr
{
  /// <summary>
  /// Servers as a container for Common Language Runtime (CLR) stored procedures.
  /// </summary>
  public static partial class StoredProcedures
  {
    /// <summary>
    /// Mimics the effect of a FOR XML AUTO clause having been applied to a stored procedure.
    /// </summary>
    /// <param name="procedureName">The name of the stored procedure to execute.</param>
    /// <param name="parameters">Any parameters which should be passed to the procedure.</param>
    /// <remarks>
    /// <para>
    /// The statement executed by this procedure is "EXECUTE [<i>procedureName</i>] 
    /// <i>parameters</i>"; the text in <i>parameters</i> should be formatted accordingly.
    /// For example, "'07 Jun 1969'" or "1234, 'Spa%'".
    /// </para>
    /// <para>
    /// This code is vulnerable to a SQL injection attack and should not be used in situations where
    /// either <i>procedureName</i> or <i>parameters</i> are supplied by end-users without thorough
    /// input validation.
    /// </para>
    /// </remarks>
    public static void ExecuteProcedureForXmlAuto(string procedureName, string parameters)
    {
      // validate our inputs
      if (string.IsNullOrEmpty(procedureName))
      {
        throw new ArgumentNullException("procedureName");
      }
      if (procedureName.IndexOfAny(new[] {'[',']','"'}) != -1)
      {
        throw new ArgumentException("The name of the stored procedure must not be delimited.", "procedureName");
      }

      // establish a connection to the server within which stored procedure is running
      using (SqlConnection sqlConnection = new SqlConnection("context connection=true"))
      {
        sqlConnection.Open();
        // create a memory stream into which we can write our XML
        using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream())
        {
          // we'll need an XmlTextWriter with which to write into the memory stream
          System.Xml.XmlTextWriter xmlTextWriter = new System.Xml.XmlTextWriter(memoryStream, System.Text.Encoding.Unicode);

          // create the command we're going to execute
          string commandText = string.Format("EXECUTE [{0}] {1}", procedureName, parameters);
          using (SqlCommand sqlCommand = new SqlCommand(commandText, sqlConnection))
          {
            // execute the command, obtaining a SqlDataReader with which to read the results
            using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
            {
              // load the contents of the SqlDataReader, as XML, into a MemoryStream
              while (sqlDataReader.Read())
              {
                xmlTextWriter.WriteStartElement(procedureName);
                for (int ordinal = 0; ordinal < sqlDataReader.FieldCount; ordinal++)
                {
                  // omit null values from the output
                  if (sqlDataReader.IsDBNull(ordinal) == false)
                  {
                    string attributeValue;
                    switch (sqlDataReader.GetDataTypeName(ordinal))
                    {
                      case "datetime":
                        // we can't use "s", the ISO 8601 format, as it omits the three-digit
                        // seconds fraction which FOR XML AUTO uses
                        attributeValue = sqlDataReader.GetDateTime(ordinal).ToString("yyyy-MM-ddTHH:mm:ss.fff", System.Globalization.CultureInfo.CurrentCulture);
                        break;
                      case "float":
                        // use a exponential format, with 15 digits after the decimal point
                        attributeValue = sqlDataReader.GetDouble(ordinal).ToString("e15", System.Globalization.CultureInfo.CurrentCulture);
                        break;
                      case "bit":
                        attributeValue = sqlDataReader.GetBoolean(ordinal) ? "1" : "0";
                        break;
                      default:
                        attributeValue = sqlDataReader.GetValue(ordinal).ToString().Trim();
                        break;
                    }
                    xmlTextWriter.WriteAttributeString(sqlDataReader.GetName(ordinal), attributeValue);
                  }
                }
                xmlTextWriter.WriteEndElement();
              }
              xmlTextWriter.Flush();
              memoryStream.Position = 0L;
            }
          }

          // send a row of meta-data back to SQL Server
          string outputColumnName = "XML_" + System.Guid.NewGuid().ToString().ToUpper(System.Globalization.CultureInfo.CurrentCulture);
          SqlDataRecord sqlDataRecord = new SqlDataRecord(new SqlMetaData(outputColumnName, SqlDbType.Xml));
          SqlContext.Pipe.SendResultsStart(sqlDataRecord);

          // now send the XML itself in a single row
          SqlXml sqlXml = new SqlXml(memoryStream);
          sqlDataRecord.SetSqlXml(0, sqlXml);
          SqlContext.Pipe.SendResultsRow(sqlDataRecord);

          // end the sending of results back to SQL Server
          SqlContext.Pipe.SendResultsEnd();
        }
      }
    }
  }
}

4. Build the project.

5. Fire up SQL Server Management Studio (or whatever Microsoft have re-named it to by the time you read this).

6. Open a new Query window, and select the database in which you wish the CLR stored procedure to be created.

7. Enable the CLR for your instance of SQL Server as follows (it doesn't matter if it's already enabled):

EXEC sp_configure 'clr enabled', '1'
GO
RECONFIGURE
GO

8. Load the assembly into SQL Server:

CREATE ASSEMBLY SqlServerClrAssembly
FROM 'C:\Users\Ian.Picknell\Documents\Blog\SQL Server\Execute Procedure For XML Auto\SqlServerClr\bin\Debug\SqlServerClr.dll'
WITH PERMISSION_SET = SAFE
GO

9. Provide SQL Server with details of the stored procedure within the assembly (specifically its name and parameters).

CREATE PROCEDURE dbo.ExecuteProcedureForXmlAuto
(
  @ProcedureName NVARCHAR(128),
  @Parameters    NVARCHAR(4000) = NULL
)
AS EXTERNAL NAME SqlServerClrAssembly.[SqlServerClr.StoredProcedures].ExecuteProcedureForXmlAuto;
GO

Note that the format of the 'AS' clause in this context is AS EXTERNAL NAME assembly_name.class_name.method_name. The class_name component must be a valid SQL Server identifier - so you'll almost always have to use delimters here.

10. Call your target stored procedure 'through' the ExecuteProcedureForXmlAuto wrapper. For example:

EXEC dbo.ExecuteProcedureForXmlAuto 'sp_configure'
GO

The above statement produced the following:

XML_880017D4-254D-4287-9B9E-496D10CA0C82

<sp_configure name="allow updates" minimum="0" maximum="1" config_value="0" run_value="0" /><sp_configure name="backup compression default" minimum="0" maximum="1" config_value="0" run_value="0" /><sp_configure name="clr enabled" minimum="0" maximum="1" config_value="1" run_value="1" /><sp_configure name="cross db ownership chaining" minimum="0" maximum="1" config_value="0" run_value="0" /><sp_configure name="default language" minimum="0" maximum="9999" config_value="0" run_value="0" /><sp_configure name="filestream access level" minimum="0" maximum="2" config_value="0" run_value="0" /><sp_configure name="max text repl size (B)" minimum="-1" maximum="2147483647" config_value="65536" run_value="65536" /><sp_configure name="nested triggers" minimum="0" maximum="1" config_value="1" run_value="1" /><sp_configure name="remote access" minimum="0" maximum="1" config_value="1" run_value="1" /><sp_configure name="remote admin connections" minimum="0" maximum="1" config_value="0" run_value="0" /><sp_configure name="remote login timeout (s)" minimum="0" maximum="2147483647" config_value="20" run_value="20" /><sp_configure name="remote proc trans" minimum="0" maximum="1" config_value="0" run_value="0" /><sp_configure name="remote query timeout (s)" minimum="0" maximum="2147483647" config_value="600" run_value="600" /><sp_configure name="server trigger recursion" minimum="0" maximum="1" config_value="1" run_value="1" /><sp_configure name="show advanced options" minimum="0" maximum="1" config_value="0" run_value="0" /><sp_configure name="user options" minimum="0" maximum="32767" config_value="0" run_value="0" />

Clicking the hyperlink then gives you XML which is (to my eye) identical to that produced by a genuine FOR XML AUTO clause:

<sp_configure name="allow updates" minimum="0" maximum="1" config_value="0" run_value="0" />
<sp_configure name="backup compression default" minimum="0" maximum="1" config_value="0" run_value="0" />
<sp_configure name="clr enabled" minimum="0" maximum="1" config_value="1" run_value="1" />
<sp_configure name="cross db ownership chaining" minimum="0" maximum="1" config_value="0" run_value="0" />
<sp_configure name="default language" minimum="0" maximum="9999" config_value="0" run_value="0" />
<sp_configure name="filestream access level" minimum="0" maximum="2" config_value="0" run_value="0" />
<sp_configure name="max text repl size (B)" minimum="-1" maximum="2147483647" config_value="65536" run_value="65536" />
<sp_configure name="nested triggers" minimum="0" maximum="1" config_value="1" run_value="1" />
<sp_configure name="remote access" minimum="0" maximum="1" config_value="1" run_value="1" />
<sp_configure name="remote admin connections" minimum="0" maximum="1" config_value="0" run_value="0" />
<sp_configure name="remote login timeout (s)" minimum="0" maximum="2147483647" config_value="20" run_value="20" />
<sp_configure name="remote proc trans" minimum="0" maximum="1" config_value="0" run_value="0" />
<sp_configure name="remote query timeout (s)" minimum="0" maximum="2147483647" config_value="600" run_value="600" />
<sp_configure name="server trigger recursion" minimum="0" maximum="1" config_value="1" run_value="1" />
<sp_configure name="show advanced options" minimum="0" maximum="1" config_value="0" run_value="0" />
<sp_configure name="user options" minimum="0" maximum="32767" config_value="0" run_value="0" />

Conclusion

There probably aren't a huge number of scenarios where you have an existing stored procedure which produces a rowset, but now need to get XML from it instead. I originally wrote a version of the above CLR stored procedure for use with BizTalk Server 2006, which required that data be returned from SQL Server as XML and my brief was that the messaging application I was developing must be capable of sourcing data from any existing stored procedure. ExecuteProcedureForXmlAuto fit the bill perfectly.

See Also

• Validating Integers in SQL Server