hi,
It's better to use the new t-sql enhancement feature 'MERGE'.
'MERGE' statement insert if the condition is false and delete or update if the condition is verified.
T-SQL is became more and more well-rounded, but PL/SQL still so fare a way
Your stored procedure should look like this :
ALTER PROCEDURE [dbo].[SP_XMLtoTABLEInsert]
(
@xmlData XML ,
@retValue varchar(20) OUTPUT
)
AS
BEGIN
BEGIN TRY
--Target table to be inserted
MERGE [Employee2] AS TARGET
USING
(
--Select data from xml variable and returned as table
SELECT
isnull(R.nref.value('policynumber[1]','VARCHAR(50)'),'') as PolicyNumber,
isnull(R.nref.value('checknumber[1]','VARCHAR(50)'),'') as CheckNumber,
isnull(R.nref.value('amount[1]','int'),'') as Amount,
R.ref.value('Collectiondate [1]', 'date') as Collectiondate,
R.ref.value('NavDate [1]', 'date') as NavDate,
R.ref.value('VendorName [1]', 'varchar(100)') as VendorName
FROM @xmlData.nodes('tma/tma_body/branchoperator') as R(nref)
)AS SOURCE
--Condition of Match
on
(
TARGET.PolicyNumber = SOURCE.PolicyNumber
and TARGET.CheckNumber = SOURCE.CheckNumber
and TARGET.Amount = SOURCE.Amount
)
--Action to do
WHEN NOT MATCHED THEN
INSERT (TARGET.PolicyNumber,TARGET.CheckNumber,TARGET.Amount,TARGET.Collectiondate,TARGET.NavDate,TARGET.VendorName)
VALUES (SOURCE.PolicyNumber,SOURCE.CheckNumber,SOURCE.Amount,SOURCE.Collectiondate,SOURCE.NavDate,SOURCE.VendorName)
;
set @retValue = 'SUCCESS';
END TRY
BEGIN CATCH
set @retValue = 'FAILURE';
END CATCH
END