CREATE PROCEDURE [dbo].[USP_REQUEST_INSERT] ( @XML_INFO NVARCHAR(MAX) = NULL ) AS BEGIN SET NOCOUNT ON BEGIN TRY BEGIN TRANSACTION -- Start the transaction --------------------------- DECLARE @REQUEST_XML INT EXEC SP_XML_PREPAREDOCUMENT @REQUEST_XML OUTPUT, @XML_INFO --1.------INSERT TO T_PROCUREMENT_HEADER-------- INSERT INTO T_EP_PROCUREMENT_HEADER(DOCNUM, IOR_NO, COST_CENTER, SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT, STATUS,APP_LEVEL, ENTERED_DATE, ENTERED_BY,REMARKS) SELECT DOCNUM, IOR_NO, COST_CENTER,SIF_ENTITY_CODE, ESTABLISHMENT_ID, PURPOSE,CURRENCY,TOTAL_AMOUNT,TAX_AMOUNT,GROSS_AMOUNT,STATUS, APP_LEVEL, getdate(), ENTERED_BY ,REMARKS FROM OPENXML (@REQUEST_XML , '/EPROC/HEADER' , 1) WITH ( DOCNUM VARCHAR(22)'DOCNUM/text()' , IOR_NO VARCHAR(22)'IOR_NO/text()' , COST_CENTER VARCHAR(10)'COST_CENTER/text()' , SIF_ENTITY_CODE VARCHAR(10)'SIF_ENTITY_CODE/text()' , ESTABLISHMENT_ID VARCHAR(10)'ESTABLISHMENT_ID/text()' , PURPOSE VARCHAR(250)'PURPOSE/text()' , CURRENCY VARCHAR(10)'CURRENCY/text()' , TOTAL_AMOUNT VARCHAR(10)'TOTAL_AMOUNT/text()' , STATUS VARCHAR(15)'STATUS/text()' , APP_LEVEL VARCHAR(100)'APP_LEVEL/text()' , TAX_AMOUNT VARCHAR(10)'TAX_AMOUNT/text()' , GROSS_AMOUNT VARCHAR(10)'GROSS_AMOUNT/text()' , ENTERED_BY VARCHAR(10)'ENTERED_BY/text()' , REMARKS VARCHAR(250)'REMARKS/text()' ) --2.-------------Insert to T_PROCUREMENT_DETAILS -------------- INSERT INTO T_EP_PROCUREMENT_DETAILS (DOCNUM,LINE_ITEM,TYPE,DESCRIPTION,EXP_CATEGORY,UOM,QUANTITY,UNIT_PRIZE,NET_AMOUNT) SELECT DOCNUM, LINE_ITEM,TYPE,DESCRIPTION, EXP_CATEGORY, UOM, QUANTITY, UNIT_PRIZE,NET_AMOUNT FROM openxml(@REQUEST_XML, '/EPROC/DETAILS', 1) WITH ( DOCNUM VARCHAR(10) 'DOCNUM /text()', LINE_ITEM VARCHAR(9) 'LINE_ITEM /text()', DESCRIPTION VARCHAR(150) 'DESCRIPTION /text()', TYPE VARCHAR(10)'TYPE/text()' , EXP_CATEGORY VARCHAR(10)'EXP_CATEGORY/text()' , UOM VARCHAR(10) 'UOM /text()', QUANTITY VARCHAR(15) 'QUANTITY/text()', UNIT_PRIZE VARCHAR(15) 'UNIT_PRIZE/text()', NET_AMOUNT VARCHAR(9) 'NET_AMOUNT/text()') SELECT 'SUCCESS' AS OUTPUT -- If we reach here , success! COMMIT END TRY --END TRY-------------------------------------------------------------- BEGIN CATCH -- Whoops , there was an error IF @@TRANCOUNT > 0 ROLLBACK -- Raise an error with the details of the exception DECLARE @ErrMsg nvarchar(4000) , @ErrSeverity int SELECT @ErrMsg = error_message() , @ErrSeverity = error_severity() RAISERROR(@ErrMsg , @ErrSeverity , 1) END CATCH SET NOCOUNT OFF END