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

Read More

How to backup all sql server 2005 databases

Backup all sql server 2005 Database using Script

DECLARE @name VARCHAR(50) -- database name  DECLARE @path VARCHAR(256) -- path for backup files  DECLARE @fileName VARCHAR(256) -- filename for backup  DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'C:\Backup\' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') 
OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name  
WHILE @@FETCH_STATUS = 0   BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name   END   

CLOSE db_cursor   DEALLOCATE db_cursor

Read More