READING XML DATA , MERGE WITH ORIGINAL DATA AND TYPE TABLE

Create table tbl_Emp(ID int,Name varchar(10),gender Bit,Address varchar(100))
truncate table tbl_Emp
CREATE proc Usp_insert_emp(@Str xml) as
Begin

Select a.b.value('@ID','int') as ID,a.b.value('@NAME','varchar(10)') as name ,a.b.value('@GENDER','bit') as gender,
a.b.value('@ADDRESS','varchar(100)') as  address1,a.b.value('@Action','CHAR(2)') as Action into #temp
FROM @str.nodes('ROOT/EMP')a(b)

-- insert new records based on insert flag
INSERT INTO tbl_Emp
select ID,name,gender,address1 from #temp where Action='I'

-- update modified records based on the update flag
UPDATE e set Name=t.name,gender=t.gender,Address=t.address1 from #temp t INNER JOIN tbl_Emp e ON t.ID=e.ID where t.Action='U'

-- delete non required records which are mentioned as flag 'D'

delete from tbl_Emp where ID in(Select ID from #temp where Action='D')
select * from tbl_emp
end



DECLARE @s xml=
'<ROOT>
<EMP ID="1" NAME="AA" GENDER="1" ADDRESS="Manikonda" Action="i"/>
<EMP ID="2" NAME="m" GENDER="1" ADDRESS="Manikonda"  Action="u"/>
<EMP ID="3" NAME="c" GENDER="1" ADDRESS="Manikonda"  Action="i"/>
<EMP ID="4" NAME="d" GENDER="1" ADDRESS="Manikonda" Action="d"/>
<EMP ID="5" NAME="e" GENDER="1" ADDRESS="Manikonda" Action="I"/>
</ROOT>'

exec Usp_insert_emp @s
----------------------------------------------------------------------------------



ALTER proc Usp_insert_emp_Parameter(@T tbl_emp_type readonly) as
Begin


-- insert new records based on insert flag
INSERT INTO tbl_Emp
select ID,name,gender,address from @T where Action='I'

-- update modified records based on the update flag
UPDATE e set NAme=t.name,gender=t.gender,Address=t.address from @T t INNER JOIN tbl_Emp e ON t.ID=e.ID where t.Action='U'

-- delete non required records which are mentioned as flag 'D'

delete from tbl_Emp where ID in(Select ID from @T where Action='D')
end

Create type tbl_emp_type as table(ID int,NAme varchar(10),gender Bit,Address varchar(100),action char(2))


declare @tt tbl_emp_type
insert into @tt select 6,'f',0,'hyd','i'
insert into @tt select 7,'f',0,'hyd','i'
insert into @tt select 2,'f',0,'hyd','D'
insert into @tt select 1,'AAA',0,'hyd','U'

exec Usp_insert_emp_Parameter @t=@tt