Prevent SqlBulkCopy to change XML value

Question

I am using SqlBulkCopy to transfer data from one database to another. My problem is that my XML column will be changed during the bulk copy.

I think that the SqlBulkCopy class is parsing/computing the XML and "simplify" the XML.

Original value:

<Item></Item>

Changed value:

<Item/>

This causes an error in my validation procedure, because the original XML is not the value which is stored in the target database.

Is there a way to prevent the SqlBulkCopy class to change my XML? Because the SQL statement is generated before without knowing that there is an XML column, I also can not convert it to nvarchar(max).

My code:

using (SqlDataAdapter adapter = new SqlDataAdapter(source_command))
{
    using (DataTable table = new DataTable())
    {
        adapter.Fill(table);
        using (SqlConnection destination = new SqlConnection(destination_connectionstring))
        {
            destination.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destination, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.CheckConstraints, null))
            {
                bulkCopy.DestinationTableName = destination_table;
                bulkCopy.BulkCopyTimeout = 1200;

                foreach (DataColumn column in table.Columns)
                {
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
                }

                try
                {
                    bulkCopy.WriteToServer(table);
                }
                catch (Exception ex)
                {
                    Log.Logger.Error(ex.ToString());
                    throw;
                }
                finally
                {
                    table.Dispose();
                }
            }
        }
    }
}

It is important that the xml-string representation is the same in the origin and target database.

Answer 1

The problem has nothing to do with the SqlBulkCopy class.

I solved my problem by adding a specific validation in the case the string-representation is not the same. If so I run a select on the sql server with an convert to XML.

Example:

SELECT
  CONVERT(XML, '<test><item></item></test>'

then I get a result like this:

<test>
   <item/>
</test>

It is also possible to command the sql server to preserve whitespaces. More to this problematic: https://dba.stackexchange.com/questions/223496/sql-server-changes-xml-structure-when-inserted