Bulk insert using sql server openxml with dynamic sql from .Net

Question

I wrote the following procedure to do bulk insert using openxml. The table name and column names are dynamic in my case as they will be created from .Net application just before this below insert. The stored procedure is running successfully but not inserting any data into the table.

Please help me

Thanks in advace.

CREATE PROCEDURE [dbo].[BulkUpdate]
@XmlDoc text,
@TableName nvarchar(50),
@ColumnNames nvarchar(4000),
@ColumnDefinition nvarchar(4000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @Idoc int;
Declare @Sql nvarchar(4000);
BEGIN TRANSACTION
Begin Try
Exec sp_xml_preparedocument @Idoc output, @XmlDoc;

Set @Sql = 'Insert into '+@TableName+'('+@ColumnNames+')

Select '+@ColumnNames+' From OpenXML(@Idoc,''/NewDataSet/Data'', 2)

With('+@ColumnDefinition+')';

Print @sql;
Exec @Sql;

Exec sp_xml_removedocument @Idoc;
Commit Transaction
End Try
Begin Catch
RollBack Transaction
End Catch

END

Answer 1

I observed that SqlClient.BulkCopy as the better performant than the above OpenXML approach. Hence I changed my logic accordingly.