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

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)

TomTom · Accepted Answer · 2012-07-08 08:51:15Z

0

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.

answered Jul 8, 2012 at 8:51

TomTom

62.4k11 gold badges95 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related