Multiple Insert Statements using OpenXML

Sometimes we need to insert multiple rows into database table, while doing this we have to maintain transactions as well. We can prepare multiple insert statements and execute it, but it will take multiple database trips (if inserting from some application) and maintaining a transaction would be bit difficult in this case.

Problem

The grid/table contains multiple rows with three checkboxes in each row. The unique id field is a hidden column and it can be accessible through code behind.

User can select number of rows in the grid and save it. Expected result is all selected rows should be inserted into database table as below.

This multiple insertion process should be done in single transaction, if any insertion fails then it should roll back all changes and return an error else commit the transaction.

Solution

We can use XML parameter and OPENXML to achieve solution for the above problem. Prepare XML as string by iterating Grid/Table.

Created XML

<RootNode>
<ChildNodes Code="00001" Edit="1" />
<ChildNodes Code="00002" Add="1" Edit="1" Delete="1" />
<ChildNodes Code="00003" Edit="1" />
<ChildNodes Code="00153" Add="1" Edit="1" Delete="1" />
<ChildNodes Code="00004" Add="1" Edit="1" Delete="1" />
</RootNode>

RootNode: Root node of XML

ChildNodes: Child Node of XML

Code, Add, Edit, Delete: Attributes of Child nodes

Stored Procedure

Create Stored Procedure in SQL Server 2005 which will accept XML as parameter and insert data within single transaction.

SET ANSI_NULLS ON
SET ANSI_PADDING ON
GO
IF OBJECT_ID('dbo.usp_AddMultipleData') IS NOT NULL
DROP PROCEDURE dbo.usp_AddMultipleData
GO
CREATE PROCEDURE [dbo].[usp_AddMultipleData]
@nAssociatedRows VARCHAR(MAX), -- This will have all Selected rwos which needs to be inserted
-- String will have data in XML Format
@nOutput BIT -- Store output result
-- E:- Error S:- Success
AS
-- Declare Transaction
DECLARE @sqlTransaction VARCHAR(20)
BEGIN TRY
-- Set output flag as succes
SET @nOutput = 'S'
-- Begin transaction
BEGIN TRANSACTION @sqlTransaction
-- Declare Handle to open XML
DECLARE @XmlDocumentHandle INT
-- Check if XML has any value then execute below steps
IF @nAssociatedRows <> ''
BEGIN
-- Open XML and create document
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT,@nAssociatedRows
-- Select records from XML and insert it into table variable
INSERT INTO Table_Name
SELECT Code,[Add],Edit,[Delete]
FROM OPENXML(@XmlDocumentHandle,'RootNode/ChildNodes',1)
WITH
(
Code VARCHAR(10),[Add] INT,Edit INT,[Delete] INT
)
-- Remove the creted document.
EXEC sp_xml_removedocument @XmlDocumentHandle
COMMIT TRANSACTION @sqlTransaction
END
END TRY
-- If error
BEGIN CATCH
-- Rollback Transaction
ROLLBACK TRANSACTION @sqlTransaction
-- Set error message
SET @nOutput = 'E'
END CATCH
GO

By executing this procedure at database end will insert all the rows passed as XML or this procedure can be called in any code behind (VB, C#).

More details about OpenXML