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)
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.