Post Data In ASP.NET Using XML

There are a lot of ways to pass data in database. But problem comes when a developer wants to pass multiple records but in a single pass. The reason is the the number of times the query is executed.Consider a situation where you want to add records in database using only a single button click but only one time execution of sql query.

This can be done by using XML datatype which is now available in MS Sql Server 2005 and 2008. To start with i would add multiple steps for easy understanding

Step 1. Declare an xml type variable in MS Sql
declare @myvar as XML

the above statement declares a variable as xml data type.

Step 2. Extract xml data in a temporary table

select cast(colx.query('data(empcode)') as varchar) as empcode from @myvar.nodes('DocumentElement/Table1') as Tabx(colx) into #tmptable

The above line extracts the data from node empcode and creates a column in temporary table #tmptable.


The whole thing can be  summarised as

CREATE
@XMLData XML
AS
BEGINBEGIN TRANSACTION
INTO
#tmpSelectionParametersFROM @XMLData.nodes('DocumentElement/Table1') AS Tabx ( Colx )
INSERT into Employees
(empcode
)
select empcode from #tmpSlectionParameters

Thats all folks hope you learnt about passing bulk data and saving in single shot.SELECT CAST(colx.query('data(empcode)') AS VARCHAR(20)) AS empcode PROC myproc