CE's Blog

In my previous post I demonstrated how to match primary keys from a source table with those generated using an INSERT…SELECT in the target table.

Warning: there is evidence that SQL Server 2005 does not preserve sort order in all cases, contrary to Microsoft’s statements in the Knowledge Base and here.

Although both articles state that the identity generation order of an ordered INSERT…SELECT between two tables in the same database is preserved, we had to learn that there is about 1% of cases (depending on input data) where the sort order is not honored. Apparently it depends on how the optimizer arranges the execution plan.
There has been a similar problem addressed to MS support, with the answer being that this faulty behavior is only to be fixed in SQL Server 2008: link.

I will keep the post for informational purposes. Sadly, due to a rare bug it cannot be used reliably in all cases. As a workaround you might want to perform INSERTs in a loop using SCOPE_IDENTITY(), or (a bit ugly) insert the temporary key into a column in the target table which has the same data type (and capturing the inserted values in the OUTPUT clause). Then update the temporarily used column with the real value. Note that the INSERT and UPDATE should both be performed in a single transaction which prevents a dirty read on the newly inserted records.

I could have named this “most annoying limitations” as well:

SQL Server 2008

 
No automatic cascading updates of foreign keys with multiple references.

The only workaround for this common scenario is to use triggers for the second column. Ouch. This even applies if the foreign keys pointing to the same primary key are not in the same table, but in different tables which have FK-relationships.
The following will not work, even when we specify ON UPDATE SET NULL for the second USER_ID foreign key.

CREATE TABLE USERS ( ID INT PRIMARY KEY IDENTITY( 0, 1 ), SURNAME INT )
CREATE TABLE ACTIONS ( 
    ID INT PRIMARY KEY IDENTITY( 0, 1 ), 
    CREATING_USER_ID INT FOREIGN KEY REFERENCES USERS( ID ) ON UPDATE CASCADE, 
    EXECUTING_USER_ID INT FOREIGN KEY REFERENCES USERS( ID ) ON UPDATE CASCADE,
    VALUE1 INT )

Fails with error: “Introducing FOREIGN KEY constraint ‘XXX’ on table ‘ACTIONS’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.”. The only thing permitted is ON UPDATE NO ACTION.

Using Table Valued Parameters With Any Type

TVPs offer big benefits when sending mass data from a client application to a SQL Server. However they are restricted to be used only in conjunction with custom types. It is not possible to use TVPs without prior declaration of a custom type – which necessitates additional changes to the database schema and permissions for every usage of a TVP.
Supported example:

CREATE TYPE dbo.MyType AS TABLE( MyColumn INT ); 
GRANT EXEC ON TYPE::dbo.MyType TO MyRole;

SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
UrlParam.SqlDbType = SqlDbType.Structured;
UrlParam.TypeName = "dbo.MyType";

More flexible solution, but not supported:

SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
UrlParam.SqlDbType = SqlDbType.Structured;
UrlParam.TypeName = "TABLE( MyColumn INT )";

Declaring Variables With Block Scope

It is as if you are forced to work with global variables as in the good old times of programming. The following looks weird, but will actually compile and run, although variable declaration and initialization are always skipped:

CREATE PROCEDURE spTEST AS
IF 0 = 1
BEGIN
    DECLARE @v INT
    SET @v = 0
END
SELECT @v

CREATE OR REPLACE / CREATE OR ALTER

No create or replace / create or alter for user defined objects. Something that makes a developer temporarily switch from ALTER PROCEDURE to CREATE PROCEDURE and back every morning after the dev database has been wiped clean.

There is a workaround, however it is a bad one:

IF OBJECT_ID( "database.schema.mytable" ) IS NOT NULL
    DROP TABLE mytable
CREATE TABLE mytable ...

In this case we do not need to modify our code depending on if the object exists or not. But what if the object exists and there are already some permissions assigned to it? Go figure.

SQL Server 2005

 
Capturing non-inserted Columns

When doing a mass insert using INSERT … SELECT, it is not possible to capture columns from the source or destination table which are not part of the insert. Is is especially useful when inserting into a table with an IDENTITY PRIMARY KEY. SQL Server 2008 improves this by introducing the MERGE statement. For a workaround, see my post on this subject.

Variable Declaration and Assignment

Only available from SQL Server 2008.

DECLARE @v INT = 0;

Instead of:

DECLARE @v INT
SET @v = 0

SQL Server 2000

 
Builtin Paging in Result Sets

Has to be done the hard way. Resolved in SQL Server 2005 by introduction of the ROW_NUMBER() OVER (…) expression:

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER ( ORDER BY id ASC ) AS ROW FROM syscolumns )
SELECT * FROM CTE WHERE ROW BETWEEN 13 AND 26

This is a workaround for SQL Server 2000, assuming that row numbers start with 1:

CREATE TABLE #TMP( ID1 INT PRIMARY KEY IDENTITY( 1, 1 ), ID2 INT )
INSERT INTO #TMP ( ID2 ) SELECT id FROM sysobjects ORDER BY crdate
SELECT o.* FROM sysobjects AS o JOIN #TMP AS t ON o.id = t.ID2 WHERE t.ID1 BETWEEN 3 AND 9
DROP TABLE #TMP

Warning: there is evidence that SQL Server 2005 does not preserve sort order in all cases, contrary to Microsoft’s statements in the Knowledge Base and here.

Although both articles state that the identity generation order of an insert between two tables in the same database is preserved, we had to learn that there is about 1% of cases (depending on input data) where the sort order is not honored. Apparently it depends on how the optimizer arranges the execution plan.
There has been a similar problem addressed to MS support, with the answer being that this faulty behavior is only to be fixed in SQL Server 2008: link.

I will keep the below post for informational purposes. Sadly, due to a rare bug it cannot be used reliably. As a workaround you might want to perform INSERTs in a loop using SCOPE_IDENTITY(), or (a bit ugly) insert the temporary key into a column in the target table which has the same data type (and capturing the inserted values in the OUTPUT clause). Then update the temporarily used column with the real value. Note that the INSERT and UPDATE should both be performed in a single transaction which prevents a dirty read on the newly inserted records.

Original post:

When inserting large amounts of data from one table to another, the INSERT … SELECT statement is usually the most efficient approach. However sometimes we need to be able to retrieve columns from the source table which were not part of the actual insert and combine them with data in the target table.

A tricky task with SQL Server 2005, which might be solved by moving from an INSERT … SELECT pattern to a row-by-row iteration over the source table (e.g. cursor) and inserting data one row at a time – which results in a big performance hit and additional log-space consumption.

However there is a solution which allows us to keep the INSERT … SELECT approach with a few modifications. For this to work, we have to build a mapping table, which has the purpose to map the primary key (or any unique column) from the source table to a unique column in the destination table.

Let us assume that we have a source and destination table which cannot me modified. The only requirement for those tables is that each has a unique column, which can serve as a key for identifying each row (e.g. primary key). For this demonstration, I am declaring those tables as table variables, because it saves us the cleanup work. Off course you can use any table, table valued function or temporary table instead, as long as it has a unique column. Note that I am intentionally declaring some weird primary key numbering intervals, that is because I want to show that the value of the primary key column does not matter.

-- Declare a source and a target table with any kind of primary key. We assume that both tables cannot be modified
DECLARE @SOURCE_TABLE  TABLE ( ID  INT PRIMARY KEY IDENTITY( 3, 5 ), SOURCE_A INT, SOURCE_B INT, SOURCE_C SYSNAME, SOURCE_D SYSNAME, SOURCE_E DATETIME )
DECLARE @TARGET_TABLE  TABLE ( ID  INT PRIMARY KEY IDENTITY( 5, 3 ), TARGET_A INT, TARGET_B INT, TARGET_C DATETIME DEFAULT GETUTCDATE() )

Next we declare a mapping table, which is supposed to map the primary key of the source table to the primary key of the target table. Again I am using a table variable here, if you are inserting large amounts of data, you may want to use a temporary mapping table instead. Note that the IDENTITY start and interval is identical with the result of the ROW_NUMBER() function.

-- The primary key of the mapping table must be compatible with ROW_NUMBER(). Use a temporary table with indexed SOURCE_ID and TARGET_ID for large inserts
DECLARE @MAPPING_TABLE TABLE ( ROW INT PRIMARY KEY IDENTITY( 1, 1 ), SOURCE_ID INT, TARGET_ID INT )

Preparation: To have some test data for demonstration, we grab a few columns from system tables and insert them into the source table.

-- Fill source table with some dummy data
INSERT INTO @SOURCE_TABLE ( SOURCE_A, SOURCE_B, SOURCE_C, SOURCE_D, SOURCE_E )
	SELECT O.id, C.[TYPE], O.name, C.name, O.crdate 
	FROM sysobjects AS O JOIN syscolumns AS C ON O.id = C.id

Here comes the main trick: While inserting, we capture the newly generated primary key in our mapping table. It is important to note that we are sorting the data to be inserted according to the unique column. After that, we update the primary key from the source table into the mapping table. Note that the primary key of the mapping table is identical with the result of ROW_NUMBER(), and the order is the same as in the insert statement.

-- Insert source into target. Capture the inserted ID, the inserted data must be sorted by primary key of the source table
INSERT INTO @TARGET_TABLE ( TARGET_A, TARGET_B )
	OUTPUT INSERTED.ID INTO @MAPPING_TABLE ( TARGET_ID )
	SELECT SOURCE_A, SOURCE_B FROM @SOURCE_TABLE
	ORDER BY ID ASC;
 
-- Update the mapping table with the ID of the source table, which we could not capture during the insert
WITH CTE AS ( SELECT ID, ROW_NUMBER() OVER ( ORDER BY ID ASC ) AS ROW FROM @SOURCE_TABLE )
UPDATE M SET M.SOURCE_ID = S.ID 
	FROM @MAPPING_TABLE AS M
	JOIN CTE AS S ON S.ROW = M.ROW

Now we have a nice mapping table which allows us to join the source and target tables together easily!

-- After the insert we can perform a join between source table and target table
SELECT M.*, S.*, T.*  
	FROM @MAPPING_TABLE AS M
	JOIN @TARGET_TABLE AS T ON T.ID = M.TARGET_ID
	JOIN @SOURCE_TABLE AS S ON S.ID = M.SOURCE_ID
	ORDER BY M.ROW ASC

The JOIN is clean and simple, and it will work with any kind of column type, as long as the values of the columns in the join uniquely identify a row in each table.

Note: SQL Server 2008 comes with a useful MERGE statement which can be used to achieve the same result with less lines of code.

This is a follow-up post to my previous explanation of writing custom Linq extension methods.

The declaration of a custom paging ObjectDataSource looks like this:

<asp:ObjectDataSource ID="SomeDataSource" EnablePaging="true" runat="server" SelectCountMethod="GetCount" 
    SortParameterName="sSortColumn" SelectMethod="GetData" TypeName="SomeNamespace+SomeClass" 
    StartRowIndexParameterName="iSkip" MaximumRowsParameterName="iTake" >
    <SelectParameters>	
        <asp:Parameter Name="sSomeParameter" Type="String" />
     </SelectParameters>	
</asp:ObjectDataSource>

In this case we need to implement a class named SomeClass which has the following public methods: GetData() and GetCount(). We make use of the non-generic IQueryable extension methods from the previous post. Note that the call of the ToArrayExtension() may be necessary to stop the defered execution built into Linq. Otherwise we could run into an exception when the Linq context is disposed (our class implements IDisposable).

public class SomeClass : IDisposable
{
    protected SomeDataContext db = new SomeDataContext();
 
    public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter )
    {
        return this.GetDataInternal( sSomeParameter ).OrderBySQLSyntax( sSortColumn ).SkipExtension( iSkip ).TakeExtension( iTake ).ToArrayExtension();
    }
 
    public int GetCount( string sSomeParameter )
    {
        return this.GetDataInternal( sSomeParameter ).CountExtension();
    }
 
    protected IQueryable GetDataInternal( string sSomeParameter )
    {
        return this.db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } );
    }
 
    public void Dispose()
    {
        this.db.Dispose();
    }
}

The above gets more useful with increased complexity of the returned data.

There is an alternative approach, which will be based on the fact that GridView/ObjectDataSource will usually call the data retrieval method first, and then call the count method. Therefore we could actually cache the value for the count method when the select method is called. This may also have a positive impact on performance. I am not sure though, if this order of calling can be relied on. Caching cannot be done in an instance variable, since ObjectDataSource apparently creates a new instance upon every call. The place where it makes most sense is withing the HttpContext.Current.Items location inside web applications. In case you are using several ObjectDataSources on one page you have to make sure the parameter name for the cached value is unique, so there are no collisions.

Making use of caching, our query can be further simplified as:

public class SomeClass
{
    public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter )
    {
        using ( SomeDataContext db = new SomeDataContext() )
        {
            var v = db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } );
            HttpContext.Current.Items[ "SomeClass.GetData" ] = v.Count();
            return v.OrderBySQLSyntax( sSortColumn ).Skip( iSkip ).Take( iTake ).ToArray();
        }
    }
 
    public int GetCount( string sSomeParameter )
    {
        object o = HttpContext.Current.Items[ "SomeClass.GetData" ];
        if ( o is int )
            return (int)o;
        throw new Exception( "Caching does not work, since GetCount() was called before GetData()!" );
    }
}

The order in which a GridView calls both methods may depend on the pagers used. Therefore a safe and well performing approach could be to combine the first and the second example.

public class SomeClass : IDisposable
{
    protected SomeDataContext db = new SomeDataContext();
 
    public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter )
    {
        var v = this.GetDataInternal( sSomeParameter );
        HttpContext.Current.Items[ "SomeClass.GetData" ] = v.CountExtension();
        return v.OrderBySQLSyntax( sSortColumn ).SkipExtension( iSkip ).TakeExtension( iTake ).ToArrayExtension();
    }
 
    public int GetCount( string sSomeParameter )
    {
        object o = HttpContext.Current.Items[ "SomeClass.GetData" ];
        if ( o is int )
            return (int)o;
        return this.GetDataInternal( sSomeParameter ).CountExtension();
    }
 
    protected IQueryable GetDataInternal( string sSomeParameter )
    {
        return this.db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } );
    }
 
    public void Dispose()
    {
        this.db.Dispose();
    }
}

Implementing custom paging with Linq and ObjectDatasource allows us to build data access layers which deliver good performance on large data while still keeping a modular and easy to maintain business object structure.
Since the data request pattern for custom paging is different from query patterns which deliver a ‘complete’ data set, additional functionality has to be implemented.

The following examples assume an ObjectDataSource in connection with an ASP.NET GridView control. When using custom paging, every DataBind() on the GridView results in two calls:

1. First, the call to the data retrieval method with selection and sort parameters plus a ‘Skip’ cound and a ‘Limit’ count to restrict the number of records returned.
2. Second, a call to a method which delivers the total amount of records available (so the control knows how many pages there are. This method also needs to receive the same selection parameters as the first method.

Basically the same filtering has to be performed in both methods, whereas the first method returns an IEnumerable, and the second returns a single integer. With an increasing number of selection parameters, a lot of redundant functionality needs to be added to both methods. So the question is, if the filtering could be performed in a single method which is then accessed by method 1 and 2.

This is easily possible if the result of the query is an existing type. Linq defered execution will enable reusing of the filtering expression for different purposes. However if the expression returns an anonymous type as an IQueryable, further processing of that type becomes more difficult.

Basically the problem is that a non-generic IQueryable is not supported by the Linq extension methods such as Count(), Skip(), Take(), OrderBy(), OrderByDescending(). Also the extension method ToArray() is not supported for a non-generic IEnumerable.

So by finding a way to implement those missing extension methods, we could save ourself implementation work and eliminate redundancies at the same time.

The following extension methods provide the missing functionality that will also work with non-generic IQueryable and IEnumerable and thus supports anonymous types.

/// This helper class will provide several Linq extension methods for sorting and custom paging.
public static class LinqExtensions
{
    /// Method used for sorting according to the pseudo SQL syntax emmitted by ObjectDataSource.
    public static IQueryable OrderBySQLSyntax( this IQueryable source, string sSQLOrder )
    {
        string[] asOrder = sSQLOrder.Split( new char[] { ' ' }, 2 );
        bool descending = ( asOrder.Length == 2 && String.Equals( asOrder[ 1 ], "DESC" ) );
        return OrderByExtension( source, asOrder[ 0 ], descending );
    }
 
    /// Used for sorting in ascending/descending order according to the property provided.
    public static IQueryable OrderByExtension( this IQueryable source, string propertyName, bool descending )
    {
        ParameterExpression x = Expression.Parameter( source.ElementType, "x" );
        LambdaExpression selector = Expression.Lambda( Expression.PropertyOrField( x, propertyName ), x );
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), descending ? "OrderByDescending" : "OrderBy",
            new Type[] { source.ElementType, selector.Body.Type }, source.Expression, selector );
        return source.Provider.CreateQuery( mce );
    }
 
    /// Adds a count expression to the query.
    public static int CountExtension( this IQueryable source )
    {
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), "Count",
            new Type[] { source.ElementType }, source.Expression );
        return source.Provider.Execute<int>( mce );
    }
 
    /// Converts the IEnumerable to an array. The main purpose is to stop the defered execution chain.
    public static IEnumerable ToArrayExtension( this IQueryable source )
    {
        MethodCallExpression mce = Expression.Call( typeof( Enumerable ), "ToArray",
            new Type[] { source.ElementType }, source.Expression );
        return source.Provider.Execute<IEnumerable>( mce );
    }
 
    /// Adds custom paging support to an IQueryable.
    public static IQueryable SkipExtension( this IQueryable source, int iSkip )
    {
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), "Skip",
            new Type[] { source.ElementType }, new Expression[] { source.Expression, Expression.Constant( iSkip ) } );
        return source.Provider.CreateQuery( mce );
    }
 
    /// Adds custom paging support to an IQueryable.
    public static IQueryable TakeExtension( this IQueryable source, int iTake )
    {
        MethodCallExpression mce = Expression.Call( typeof( Queryable ), "Take",
            new Type[] { source.ElementType }, new Expression[] { source.Expression, Expression.Constant( iTake ) } );
        return source.Provider.CreateQuery( mce );
    }
}

If the above appears to be too complicated, there is an alternative approach, which will be based on the fact that ObjectDataSource will call the data retrieval method first, and then call the count method. Therefore we could actually cache the value for the Count() method when the select method is called. This would also have a positive impact on performance. I am not sure though, if this order of calling can be relied on. Caching cannot be done in an instance variable, since ObjectDataSource apparently creates a new instance upon every call. The place where it makes most sense is withing the HttpContext.Current.Items location inside web applications. In case you are using several ObjectDataSources on one page you have to make sure the parameter name for the cached value is unique, so there are no collisions. See my next post for details.

The following sample code shows how to return a resultset from a CLR stored procedure.
The main thing to note is that you have to pass an empty SqlDataRecord to the SendResultsStart() method in order to provide feedback on the columns sent back to the caller.

[SqlProcedure]
public static SqlInt32 MyStoredProcedure()
{
    SqlMetaData[] meta = new SqlMetaData[] { new SqlMetaData( "MyColumn", SqlDbType.Int ) };
    Int32[] data = new Int32[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
    SqlDataRecord[] records = data.Select( x => { SqlDataRecord r = new SqlDataRecord( meta ); r.SetInt32( 0, x ); return r; } ).ToArray();
 
    SqlContext.Pipe.SendResultsStart( new SqlDataRecord( meta ) );
    foreach ( SqlDataRecord r in records )
        SqlContext.Pipe.SendResultsRow( r );
    SqlContext.Pipe.SendResultsEnd();
 
    return records.Length;
}

In case you would like to return data retrieved from the database using a SqlDataReader object, you can directly pass the reader to the SqlContext.Pipe.Send() method:

[SqlProcedure]
public static void MyStoredProcedure()
{
    using ( SqlConnection con = new SqlConnection( "context connection=true" ) )
    {
        con.Open();
        using ( SqlCommand cmd = con.CreateCommand() )
        {
            cmd.CommandText = "SELECT SomeColumn FROM SomeTable WHERE SomeColumn IS NOT NULL";
            using ( SqlDataReader r = cmd.ExecuteReader() )
                SqlContext.Pipe.Send( r );
        }
    }
}

SQL Server 2008 supports a new way of passing a variable number of arguments to functions and stored procedures.
The good thing for .NET developers is that those table-valued parameters are also usable from a managed context.
So when doing a mass join/mass update using SqlCommand, this approach offers a huge increase in performance when migrating from a line-by-line approach to a single statement that is to be executed.

The following example is to demonstrate a sample usage of this technique. We are assuming that we want to insert a set of Unicode strings into the database.

First of all, TVPs are only supported with custom types that must be created prior to execution.

CREATE TYPE dbo.MyType AS TABLE( MyColumn NVARCHAR(MAX) ); 
GRANT EXEC ON TYPE::dbo.MyType TO MyRole;

This is kind of a drawback if you are using a large number of different data types from different queries.

Here is how to pass data from an array to SQL Server:

using ( SqlConnection con = new SqlConnection( "connection string" ) )
{
    con.Open();
    using ( SqlCommand cmd = con.CreateCommand() )
    {
        string[] arr = { "a", "b", "c", "d" };
        SqlMetaData meta = new SqlMetaData( "MyColumn", SqlDbType.NVarChar, SqlMetaData.Max );
        var records = arr.Select( x => { SqlDataRecord r = new SqlDataRecord( meta ); r.SetString( 0, x ); return r; } );
 
        SqlParameter UrlParam = cmd.Parameters.AddWithValue( "@MyTVP", records );
        UrlParam.SqlDbType = SqlDbType.Structured;
        UrlParam.TypeName = "dbo.MyType";
        cmd.CommandText = "INSERT INTO MyTable ( SomeColumn ) SELECT MyAlias.MyColumn FROM @MyTVP AS MyAlias;"; 
        cmd.ExecuteNonQuery();
    }
}

In order to send the array data to the database server, we need to wrap it in an IEnumerable. This is then added to the parameters collection with the special type qualifier SqlDbType.Structured. On the SQL side, the handling is the same as a real table in the database, although it might be necessary to work with aliases when referencing the TVP.

Note that in this example we are limiting the insert to a single column. However you may also create TVPs with multiple columns and different data types.

This approach does not offer much savings when dealing with a few records to be passed to the server. However when you want to insert a few hundred records at once, executing hundreds of queries instead of a single one pays off.

The System.Data.SqlClient namespace does not include a straightforward way of sending an arbitrary amount of data rows to SQL Server in a single statement, e.g. for making a mass updates/bulk insert.

In most circumstances, generating SQL statements dynamically using string concatenation would be a bad choice, since it might offer vectors for SQL injection and other pitfalls resulting from inadequately escaped code.

When using SQL Server 2008, table valued parameters come in handy, however they require modifications on the server which may not be eligible in all scenarios.

There is another possibility using SQL Server’s builtin XML data type together with the System.Xml classes to achieve this goal. The following code demonstrates a simple approach for inserting data from a string array using a single SQL statement.

using ( SqlConnection con = new SqlConnection( "connection string" ) )
{
    con.Open();
    using ( SqlCommand cmd = con.CreateCommand() )
    {
        string[] arr = { "a", "b", "c", "d" };
        XmlDocument xml = new XmlDocument();
        xml.AppendChild( xml.CreateElement( "MyColumns" ) );
        foreach ( string s in arr )
        {
            XmlElement e = xml.CreateElement( "MyColumn" );
            e.InnerText = s;
            xml.DocumentElement.AppendChild( e );
	}
 
        cmd.AddParameter( "@XML", DbType.Xml, xml.OuterXml );
        cmd.CommandText = 
            "INSERT INTO MyTable ( SomeColumn ) SELECT Item.value( '.', 'NVARCHAR(MAX)' ) " + 
            "FROM @XML.nodes( 'MyColumns/MyColumn' ) AS x( Item );";
        cmd.ExecuteNonQuery();
    }
}

Since preparing data with an XmlDocument can require a lot of memory with large data, the following alternative can be considered when passing a string array:

/// <summary>Serializes the given strings to an XML string. Will use short element names to reduce string size.</summary>
public static string SerializeToXML( IEnumerable<string> strings )
{
    using ( StringWriter sw = new StringWriter() )
    using ( XmlTextWriter xw = new XmlTextWriter( sw ) )
    {
        foreach ( string s in strings )
        {
            xw.WriteStartElement( "s" );
            xw.WriteString( s );
            xw.WriteEndElement();
        }
        xw.Flush();
        return sw.ToString();
    }
}

When an entire object needs to be passed to the database, the following generic solution works well:

/// <summary>Serializes the given objects to an XML string. Will use XmlSerializer which requires more memory.</summary>
public static string SerializeToXMLUsingXmlSerializer<T>( IEnumerable<T> t )
{
    using ( StringWriter sw = new StringWriter() )
    using ( XmlWriter xw = XmlWriter.Create( sw, new XmlWriterSettings() { OmitXmlDeclaration = true, Indent = false } ) )
    {
        T[] tmp = t.ToArray();
        XmlSerializer xs = new XmlSerializer( tmp.GetType(), new XmlRootAttribute( typeof( T ).Name ) );
        XmlSerializerNamespaces ns = new XmlSerializerNamespaces();
        ns.Add( String.Empty, String.Empty );
        xs.Serialize( xw, tmp, ns );
        return sw.ToString();
    }
}

When only sending a few rows to the server, this approach does not offer better performance that an iterative approach. However when dealing with larger amounts of data, the overhead of sending, executing, receiving and evaluating each query will add up to a signifficant amount of time – which can be avoided using this technique.