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.