bulk insert record using xml dynamic query

 In this article, we are going to see, how to bulk insert records using xml dynamic query

CREATE PROC SPH_XMLInsert

@tblnm VARCHAR(250),

@spnm VARCHAR(250)

AS

SET NOCOUNT ON;

DECLARE @tblstructure TABLE

(

colid INT IDENTITY(1,1),

name  VARCHAR(1000),

dtype VARCHAR(1000)

);

INSERT INTO @tblStructure

SELECT 

  name         = c.[name]

 ,dtype        = 

    CASE 

      WHEN tp.[name] IN ('varchar', 'char') THEN tp.[name] + '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length AS VARCHAR(25))) + ')' 

      WHEN tp.[name] IN ('nvarchar','nchar') THEN tp.[name] + '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length / 2 AS VARCHAR(25)))+ ')'      

      WHEN tp.[name] IN ('decimal', 'numeric') THEN tp.[name] + '(' + CAST(c.[precision] AS VARCHAR(25)) + ', ' + CAST(c.[scale] AS VARCHAR(25)) + ')'

      WHEN tp.[name] IN ('datetime2') THEN tp.[name] + '(' + CAST(c.[scale] AS VARCHAR(25)) + ')'

      ELSE tp.[name]

    END

FROM sys.tables t 

JOIN sys.schemas s ON t.schema_id = s.schema_id

JOIN sys.columns c ON t.object_id = c.object_id

JOIN sys.types tp ON c.user_type_id = tp.user_type_id

WHERE s.[name] = 'dbo' AND t.[name] = @tblnm

DECLARE @counter INT = 1,@maxCol INT = 0,@name VARCHAR(100)='',@dtype VARCHAR(100)='',@dlength VARCHAR(100)='',@colname VARCHAR(MAX) = '';

SELECT @colname = @colname + CAST(name  AS VARCHAR(500)) +  ',' FROM @tblstructure

SELECT @maxCol = MAX(colid) FROM @tblStructure;

PRINT 'CREATE PROCEDURE DBO.'+@spnm+' 

   @XMLData XML = NULL

AS

SET ARITHABORT ON;

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET XACT_ABORT ON

BEGIN TRY

   DECLARE @ErrorMessage  NVARCHAR(4000),@ErrorNumber INT,

   @ErrorSeverity INT,@ErrorState INT,

   @ErrorLine     INT,@ErrorProcedure NVARCHAR(200);'

PRINT + CHAR(13) + CHAR(10) 

PRINT SPACE(3)+'IF OBJECT_ID(''TEMPDB.DBO.#TEMPTBL'') IS NOT NULL

   BEGIN

DROP TABLE dbo.#TEMPTBL

   END

'+SPACE(3)+'SELECT'

WHILE (@counter <= @maxCol)

BEGIN

SELECT @name = name, @dtype = dtype FROM @tblStructure WHERE colid = @Counter;

IF @counter = 1

BEGIN

PRINT SPACE(4)+'Tbl.Cols.value('+''''+ @name +'[1][not(@xsi:nil = "true")]'',' +''''+ upper(@dtype) + ''''+') AS ' + @name 

END

ELSE

BEGIN

PRINT SPACE(3)+',Tbl.Cols.value('+''''+ @name +'[1][not(@xsi:nil = "true")]'',' +''''+ upper(@dtype) + ''''+') AS ' + @name 

END

SET @Counter = @Counter + 1;

END

PRINT SPACE(3)+'INTO #TEMPTBL

'+SPACE(3)+'FROM @XMLData.nodes(''//ArrayOfTEMPTBL//TEMPTBL'') Tbl(Cols)'

PRINT + CHAR(13) + CHAR(10) 

PRINT SPACE(3)+'DECLARE @LOCAL BIT

   SET @LOCAL = 0

   IF(@@TRANCOUNT=0)

   BEGIN

      BEGIN TRANSACTION

      SET @local = 1

   END'

PRINT + CHAR(13) + CHAR(10)

PRINT SPACE(3)+'INSERT INTO '+@tblnm+'('+ SUBSTRING(@colname, 1, LEN(@colname)-1) +')'

PRINT SPACE(3)+'SELECT '+ SUBSTRING(@colname, 1, LEN(@colname)-1) +' FROM #TEMPTBL'

PRINT + CHAR(13) + CHAR(10)

PRINT SPACE(3)+'IF (@@TRANCOUNT>0 AND @LOCAL=1)

   BEGIN

   IF (XACT_STATE()) = 1

      BEGIN

     COMMIT TRANSACTION

      END

   END 

END TRY'

PRINT 'BEGIN CATCH

   IF (@@TRANCOUNT>0 and @LOCAL=1)

   BEGIN

      IF (XACT_STATE()) = -1

         BEGIN

         ROLLBACK TRANSACTION

         SELECT @ErrorNumber = ERROR_NUMBER(),@ErrorSeverity = ERROR_SEVERITY(),

         @ErrorState = ERROR_STATE(),@ErrorLine = ERROR_LINE(),

         @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), ''-'');

         SELECT @ErrorMessage = ERROR_MESSAGE();

         RAISERROR (@ErrorMessage,@ErrorSeverity, 1,@ErrorNumber, @ErrorSeverity, @ErrorState,@ErrorProcedure,@ErrorLine);

      END

   END

END CATCH'