SqlBulkCopy fails trying to copy a row with large content in an XML column

I am trying to copy a record from one SQL Server table to another. Both tables have the same structure, one of the columns is of type xml. Source table has a large xml content in of the rows, about 2.5Mb.

I saved the content of the xml column into a file, see map.zip attached or download it from https://docs.google.com/leaf?id=0Bz4PXXEQL5TpM2U5MWJhM2MtMTI0Yi00ODg0LTk4OWItMzJiNjVjMDIzNjc2&hl=en&authkey=CLT5i8oP

Simplified version of my code:

string query = "select * from MyTableSource where id = 1";

using (SqlConnection targetConnection = new SqlConnection(connectionStringTarget))
{
targetConnection.Open();

using (SqlConnection sourceConnection = new SqlConnection(connectionStringSource))
{
    sourceConnection.Open();

    using (SqlCommand command = new SqlCommand(query, sourceConnection))
    {
    using (IDataReader rea开发者_如何学运维der = command.ExecuteReader(CommandBehavior.SingleResult))
    {
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(targetConnection))
        {
        bulkCopy.DestinationTableName = "MyTableTarget";
        bulkCopy.WriteToServer(reader);
        }
    }
    }
} 
}

Exception that occurs on bulkCopy.WriteToServer:

System.Data.SqlClient.SqlException was unhandled
Message=XML parsing: Document parsing required too much memory
Source=.Net SqlClient Data Provider
ErrorCode=-2146232060
Class=16
LineNumber=1
Number=6303
Procedure=""
Server=myserver
State=1
StackTrace:
     at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
     at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
     at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
     at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
     at SyncTest.Form1.buttonCopyXml_Click(Object sender, EventArgs e) in C:\..\Form1.cs:line 2251
     at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
     at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
     at System.Windows.Forms.Control.WndProc(Message& m)
     at System.Windows.Forms.ButtonBase.WndProc(Message& m)
     at System.Windows.Forms.Button.WndProc(Message& m)
     at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
     at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
     at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
     at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
     at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
     at SyncTest.Program.Main() in C:\..\Program.cs:line 18
     at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
     at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
     at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
     at System.Threading.ThreadHelper.ThreadStart()
InnerException: 

It looks like a SqlBulkCopy bug. I wonder if somebody else can reproduce and confirm that.

Update: Filed it to Microsoft,

_https://connect.microsoft.com/VisualStudio/feedback/details/614046/sqlbulkcopy-fails-trying-to-copy-a-row-with-large-content-in-an-xml-column

they confirmed this is their bug:

From debugging thus far, looks like an issue with server side processing of XML in the bulk copy path. One of the attributes in the XML file is really large and this is causing SQL Server to fail when processing the XML because of an allocation size limit.

Change your select query to retrieve the xml column as a [n]varchar(max) column type, to avoid the xml processing overhead, and probably the bug as well.