2017/11/10

Use XML Data For Save in Database

CREATE PROCEDURE [dbo].[SaveData]
@MH INT,
@TxTest INT,
@ID INT,
@CreatedBy INT,
@strXML VARCHAR(MAX)
AS
BEGIN

SET NOCOUNT ON;

DECLARE @XMLDocHandle INT;

DECLARE @InvestorID INT
SET @ID=(SELECT Id FROM tbl_Test WHERE  ID=@ID)

IF(@strXML IS NOT NULL AND @strXML<>'')
BEGIN
EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @strXML

CREATE TABLE #temp(ID INT,ClientID INT,InvID INT,Name VARCHAR(100),MaxAmount NVARCHAR(20),BankName NVARCHAR(500),
AN VARCHAR(30),Status VARCHAR(20),CreatedDate DATETIME,CreatedBy VARCHAR(10),StartDate DATETIME)

INSERT INTO #temp
SELECT ID,ClientID,@InvestorID,MName,MA,BName,AN,Status,GETDATE(),@CreatedBy,GETDATE()
FROM OPENXML (@XMLDocHandle, 'NewDataSet/Table1',2) 
WITH(
ID VARCHAR(10) 'ID',
ClientID VARCHAR(10) 'ClientID',
MN VARCHAR(100) 'MN',
MA NVARCHAR(20) 'MA',
BN NVARCHAR(500) 'BN',
ANumber VARCHAR(30) 'ANumber',
Status VARCHAR(20) 'Status',
CreatedBy VARCHAR(10) 'CliID'
)


MERGE INTO IMan IM
USING #temp T ON IM.InvestorID=T.InvID
WHEN MATCHED AND IM.InvestorID=T.InvID THEN

UPDATE SET
IM.CliID=T.ClientID,
IM.InID=T.InvestorID,
IM.MName=T.MName
WHEN NOT MATCHED  THEN
INSERT (ClID,IID,MName,MAmount,BName,ANumber,Status,CreatedDate,CreatedBy,SponsorBank,
SpCode,UCode,CompanyName,Action,DAmount,SOrRefNo,Frequency,UCancelled,CustomerAdditionalInfo,StartDate)
VALUES(T.ClientID,T.InvestorID,T.MandateName,T.MAmount,T.BName,T.ANumber,T.Status,T.CreatedDate,T.CreatedBy,
'HD','Xyz','Xyz','Xyz','Debit','Upto Max Amount',T.MName,'As and when Presented',
1,'NA',T.StartDate);

SELECT 1 'Status'
END


END