how to create insert and update store procedure with xml in sql server

In this article , we are going to see how to create insert and update store procedure with xml in sql server

IF OBJECT_ID('tempdb..#EMP') IS NOT NULL DROP TABLE #EMP

GO

CREATE TABLE #EMP

(

 [ID] [INT] IDENTITY(1,1) NOT NULL,

 [NAME] [VARCHAR](50)  NULL,

 [AGE] [INT] NULL,

 [SALARY] [BIGINT] NULL

)

GO

--INSERT INTO #EMP ([NAME], [AGE], [SALARY]) VALUES (N'DEV2', 23, 35000)

GO

/* 

BEGIN TRAN 

--ROLLBACK 

EXEC USP_Insert_EMPTemp_Form_Detail '

<EMPS>

<EMP> 

  <NAME>AJAY</NAME> 

  <AGE>22</AGE> 

  <SALARY>55000</SALARY> 

</EMP>

<EMP> 

  <NAME>DEV</NAME> 

  <AGE>25</AGE> 

  <SALARY>65000</SALARY> 

</EMP>

</EMPS>

' 

*/

IF OBJECT_ID('USP_Insert_EMPTemp_Form_Detail','P') IS NOT NULL DROP PROC USP_Insert_EMPTemp_Form_Detail

GO

CREATE PROC USP_Insert_EMPTemp_Form_Detail 

@XML TEXT 

AS

BEGIN 

SET NOCOUNT ON;

SET XACT_ABORT ON; 

DECLARE @hDoc1 INT;             

EXEC sp_xml_preparedocument @hDoc1 OUTPUT, @XML 

BEGIN TRY

   BEGIN TRANSACTION;

   INSERT INTO #EMP(NAME,AGE,SALARY) 

   SELECT E.NAME,E.AGE,E.SALARY FROM OPENXML(@hDoc1,'/EMPS/EMP',2) 

   WITH 

   ( 

   NAME VARCHAR(50) 'NAME',   

   AGE INT 'AGE', 

   SALARY BIGINT 'SALARY'   

   )E

    

   IF (XACT_STATE()) = 1  

   BEGIN

  SELECT 1 AS STATUS;

  COMMIT TRANSACTION;    

   END 

END TRY   

BEGIN CATCH   

    IF (XACT_STATE()) = -1  

BEGIN 

SELECT 0 AS STATUS;

ROLLBACK TRANSACTION;

END

END CATCH ;

END

GO

/*

BEGIN TRAN

--ROLLBACK

EXEC USP_Update_EMPTemp_Form_Detail '

<EMPS>

<EMP>

  <ID>1</ID>

  <NAME>AJAY</NAME>

  <AGE>22</AGE>

  <SALARY>65000</SALARY>

</EMP>

<EMP>

  <ID>2</ID>

  <NAME>DEV</NAME>

  <AGE>25</AGE>

  <SALARY>75000</SALARY>

</EMP>

</EMPS>

'

*/

IF OBJECT_ID('USP_Update_EMPTemp_Form_Detail','P') IS NOT NULL DROP PROC USP_Update_EMPTemp_Form_Detail

GO

CREATE PROC USP_Update_EMPTemp_Form_Detail

@XML TEXT

AS             

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT ON;             

DECLARE @hDoc1 INT;             

EXEC sp_xml_preparedocument @hDoc1 OUTPUT, @XML             

BEGIN TRY

BEGIN TRANSACTION;

UPDATE #EMP SET #EMP.NAME=T.NAME ,#EMP.AGE =T.AGE,#EMP.SALARY=T.SALARY

FROM (SELECT ID,NAME,AGE,SALARY FROM OPENXML(@hDoc1 ,'/EMPS/EMP',2) 

WITH

(

ID INT 'ID', 

NAME VARCHAR(50) 'NAME',   

AGE INT 'AGE',

SALARY BIGINT 'SALARY' 

))T

WHERE #EMP.ID=T.ID

IF (XACT_STATE()) = 1  

BEGIN 

SELECT 1 AS STATUS

COMMIT TRANSACTION;

END

END TRY

BEGIN CATCH

IF (XACT_STATE()) = -1  

BEGIN 

SELECT 0 AS STATUS;

ROLLBACK TRANSACTION;

END

END CATCH

END