Using SqlBulkCopy to insert rows to a table having column of type 'xml' in SQL Server 2008 raises exception

c# sqlbulkcopy sql-server

Question

I have simple table in SQL Server with two columns Id (BIGINT), PublishData (XML).

I am interested in using SqlBulkCopy but when I try to so using SqlBulkCopy.WriteToServer(DataTable), it gives me below exception. I am using SqlXml data type in C# when I create datatable and add column for PublishData. I think it mismatches with column type xml in SQL Server. I need to know what is the correct datatype I need to use while creating the datatable for xml column and then use it with SqlBulkCopy.

Any help would be appreciated?

Unhandled Exception: System.InvalidCastException: Specified cast is not valid.
at System.Data.SqlClient.TdsParser.WriteBulkCopyValue(Object value, SqlMetaDa taPriv metadata, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount )
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowSt ate rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

Popular Answer

need to know what is the correct datatype I need to use while creating the datatable for xml column and then use it with SqlBulkCopy

What about tying it out instead of asking here? I mean, seriously, how many data types ARE there?

I would suggest you try a STRING in .NET ;) Kind of obvious, isn't it - you can hardly store XML in a Bool or Integer data type, so the ONLY alternative to SqlXml I can see would be string.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why