how to insert the multipe records in sql server with xml data

while inserting multiple records at a same time in a single transaction without failure (eg: bulk booking cinema tickets) means how to do this. for that inserting datas in sql server via xml is better.By using this we can also stop the transaction failure.................

The following example shows how to insert the multiple records using xml data

CREATE TABLE [dbo].[empinfo](
    [eno] [int] NULL,
    [ename] [varchar](50)
)
Stored procedure:

Create  Procedure sp_InsertEmp

@in_XML Text
AS
Begin

DECLARE @XMLHandle AS INTEGER  

Declare  @temp table
(empno int,
 empname varchar(20)
)
----------------------------------------------------------------
-- Initialize the XML for Processing
----------------------------------------------------------------
EXECUTE sp_xml_preparedocument
        @XMLHandle      OUTPUT
      , @in_XML

INSERT @Temp
(
      empno,
        empname      
)
SELECT
    XMLInput.eno,
    XMLInput.ename  
FROM OPENXML(@XMLHandle,'NewDataset/employee',2)
WITH
(
      eno int,
        ename varchar(20)
)as XMLInput

----------------------------------------------------------------
-- Close the XML
----------------------------------------------------------------
EXECUTE sp_xml_removedocument @XmlHandle


insert into empinfo(eno,ename)

select empno,empname from @temp

End



--execute stored procedure

exec sp_InsertEmp

'<NewDataset>
<employee>
<eno>101</eno>
<ename>sidhu</ename>
</employee>
</NewDataset>'



Related Question :

1) how to insert the multiple records using c# and sqlserver
2) how to To Send Multiple Rows At Once Into A Stored Procedure
3) Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0

Posted in: c# snippets , Sql Server