ADO.NET XML type column in Table Value Parameter
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["Ans开发者_运维技巧wer"] = Encoding.UTF8.GetBytes(assignment.Answer);
The ADO.NET engine knows how to convert the bytes to XML. Hope that helps someone!
精彩评论