ADO.NET XML type column in Table Value Parameter

Question

Hi,

This isn't actually a question, it's a problem which I couldn't find any help on, so I thought I'd post the solution I eventually found in case someone else encounters it.

The problem was with an XML-typed column in a TVP parameter I was passing to a SQL Server 2008 stored proc. When I called ExecuteNonQuery() I got an error that was like

Additional information: XML parsing: (line number blah blah) unable to switch the encoding (blah blah)

This is because the XML as usual, was designated as UTF-8

<?xml version="1.0" encoding="UTF-8" ?>

but strings in .NET are UTF-16. The solution was to declare the column with XML as a byte array in the DataTable:

hitImport.Columns.Add("Answer", typeof(byte[]));

and then get the UTF-8 bytes when adding the row:

row["Answer"] = Encoding.UTF8.GetBytes(assignment.Answer);

The ADO.NET engine knows how to convert the bytes to XML. Hope that helps someone!