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.

Its still valid XML, representing exactly the same data, so you should change your validation to handle that — Dale K, Aug 23 '21 at 07:28
Of course it is, but the processing system assumes that it is exactly the same notation. A change in the validation process is not possible in this regard due to the limitation of the processing system. Unfortunately, I cannot ignore this requirement — Robert Wolf, Aug 23 '21 at 07:31
`Unfortunately, I cannot ignore this requirement.` Unfortunately, `SqlBulkCopyOptions` doesn't have a `CaterForPoorlyWrittenProcessingSystemsThatTreatXmlAsText` flag. If you "cannot" change your validation procedure, and you "cannot" change your column datatype from `xml` to `nvarchar(max)`, then you cannot use `SqlBulkCopy`. — Ian Kemp, Aug 23 '21 at 08:23
What is the datatype of the column in `table`, how are you passing it? You could maybe pass an `XmlDocument` with `PreserveWhitespace = true` — Charlieface, Aug 23 '21 at 10:07

score 0 · Accepted Answer · answered Aug 24 '21 at 05:24

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

Prevent SqlBulkCopy to change XML value

1 Answers1