读取XML并使用存储过程插入SQL表如果记录已存在而不是不插入 - 仅通过存储过程 [英] Read the XML and insert into SQL Table using stored procedure If record already exist than do not insert - Only through Stored Procedure
问题描述
我的查询: - 在此查询中,它仅适用于如果再次解析整个XML,而不会插入重复记录,但是如果您更改XML中的3条记录中的1条记录,那么它将再次插入所有记录,这意味着表中的重复记录,可以任何人请帮助我如何避免这个问题。
代码: -
< pre lang =sql> ALTER PROCEDURE [dbo]。[SP_XMLtoTABLEInsert]
(
@ xmlData XML,
@ retValue varchar ( 20 ) OUTPUT
)
AS
- SET @retValue = NULL;
DECLARE @ PolicyNumber
DECLARE @ CheckNumber VARCHAR ( 20 )
DECLARE @ Amount INT
SELECT
@ PolicyNumber = ISNULL(nref.value(' < span class =code-string> PolicyNumber [1]',' VARCHAR(50)'),' '),
@ CheckNumber = ISNULL(nref.value( ' CheckNumber [1]',' VARCHAR(50)'),' '),
@ Amount = ISNULL(nref.value(' Amount [1]',' int'),' ' )
FROM @ xmlData .nodes(' tma / tma_body / branchoperator') as R(nref)
BEGIN
IF EXISTS ( SELECT 1 来自 [Employee2] 其中 PolicyNumber = @ PolicyNumber 和 CheckNumber = @ CheckNumber 和金额= @ Amount )
BEGIN
SET @ retValue = ' 记录已存在';
END
ELSE IF NOT EXISTS ( SELECT 1 来自 [Employee2] 其中 PolicyNumber = @ PolicyNumber 和 CheckNumber = @ CheckNumber 和金额= @ Amount )
BEGIN
INSERT INTO [Employee2](PolicyNumber,CheckNumber,Amount,Collectiondate ,NavDate,VendorName)
SELECT
(R.ref.value(' PolicyNumber [1]',' varchar(20)' )) as ' PolicyNumber',
R.ref.value(' CheckNumber [1]',' varchar(20)') as ' CheckNumber',
R.ref.value( ' 金额[1]',' int') as ' 金额',
R.ref.value(' Collectiondate [1]',< span class =code-str ing>' date') as ' Collectiondate',
R.ref.value(' NavDate [1]',' date')< span class =code-keyword> as ' NavDate',
R.ref.value(' VendorName [1]',' varchar(100)') as ' VendorName'
FROM @xmlData .nodes(' tma / tma_body / branchoperator') as R(ref)
IF ( @@ ROWCOUNT > 0 )
SET @ retValue = ' SUCCESS';
END
END
我的InPut XML: -
声明 @ retValue1 varchar ( 50 );
声明 @ XmlStr XML;
SET @ XmlStr = ' < tma> ;
< tma_header>
< conversationid />
< transaction> BIGXML< / transaction>
< sourcecountry> India< / sourcecountry>
< sourcecompany> MLIN< / sourcecompany>
< sourcesystem> METWS< / sourcesystem>
< uniquekey> 544010844< / uniquekey>
< / tma_header>
< tma_body>
< branchoperator>
< policynumber> 123456< / policynumber>
< checknumber> 544010844< / checknumber>
< amount> 17000< / amount>
< collectiondate> 03/07 / 2014< / collectiondate>
< navdate> 03/07 / 2014< / navdate>
< vendorname> Bhadri< / vendorname>
< / branchoperator>
< branchoperator>
< policynumber> 789101< / policynumber>
< checknumber> 5477866876< / checknumber>
< amount> 17000< / amount>
< collectiondate> 04/07 / 2014< / collectiondate>
< navdate> 04/07 / 2014< / navdate>
< vendorname> Sandeep< / vendorname>
< / branchoperator>
< branchoperator>
< policynumber> 5678866< / policynumber>
< checknumber> 54325668786< / checknumber>
< amount> 17000< / amount>
< collectiondate> 07/07/2014< / collectiondate>
< navdate> 07/07/2014< / navdate>
< vendorname> Suman< / vendorname>
< / branchoperator>
< / tma_body>
< / tma>';
EXEC [SP_XMLtoTABLEInsert] @ xmlData = @ XmlStr,@ retValue = @ retValue1 OUTPUT
print @ retValue1
最好使用新的t-sql增强功能'MERGE'。
'MERGE '如果条件为false则插入语句,如果条件已验证则删除或更新。
T-SQL变得越来越全面,但是PL / SQL仍然如此票价
您的存储过程应如下所示:
ALTER PROCEDURE [dbo]。[SP_XMLtoTABLEInsert]
(
@xmlData XML,
@retValue varchar(20)OUTPUT
)
AS
BEGIN
BEGIN TRY
- 要插入的目标表
MERGE [Employee2] AS TARGET
使用
(
- 从xml中选择数据变量并作为表返回
SELECT
isnull(R.nref.value('policynumber [1]','VARCHAR(50)'),'')作为PolicyNumber,
isnull(R。 nref.value('checknumber [1]','VARCHAR(50)'),'')作为CheckNumber,
isnull(R.nref.value('amount [1]','int'),' ')作为金额,
R.ref.value('Collectiondate [1]','date')作为Collectiondate,
R.ref.value('NavDate [1]','date')作为NavDate,
R.ref.value('VendorName [1]','varchar(100)')作为VendorName
FROM @ xmlData.nodes('tma / tma_body / branchoperator')作为R( nref)
)AS SOURCE
-
上的匹配
的条件(
TARGET.PolicyNumber = SOURCE.PolicyNumber
和TARGET.CheckNumber = SOURCE。 CheckNumber
和TARGET.Amount = SOURCE.Amount
)
- 行动做
当没有匹配那么
INSERT(TARGET.PolicyNumber,TARGET.CheckNumber,TARGET .Amount,TARGET.Collectiondate,TARGET.NavDate,TARGET.VendorName)
VA LUES(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
My Query :- In this query it works only If the entire XML is parsed again, than it will not insert duplicate records , But If you change the 1 record out of 3 records in XML than it will insert all there records again, that means again duplicate records in table, Could any one please help me how can I avoid this problem.
Code :-
ALTER PROCEDURE [dbo].[SP_XMLtoTABLEInsert]
(
@xmlData XML ,
@retValue varchar(20) OUTPUT
)
AS
--SET @retValue =NULL;
DECLARE @PolicyNumber VARCHAR(20)
DECLARE @CheckNumber VARCHAR(20)
DECLARE @Amount INT
SELECT
@PolicyNumber=ISNULL(nref.value('PolicyNumber[1]','VARCHAR(50)'),''),
@CheckNumber=ISNULL(nref.value('CheckNumber[1]','VARCHAR(50)'),''),
@Amount=ISNULL(nref.value('Amount[1]','int'),'')
FROM @xmlData.nodes('tma/ tma_body / branchoperator') as R(nref)
BEGIN
IF EXISTS (SELECT 1 from [Employee2] where PolicyNumber = @PolicyNumber and CheckNumber = @CheckNumber and Amount = @Amount)
BEGIN
SET @retValue='Record Already Exist';
END
ELSE IF NOT EXISTS (SELECT 1 from [Employee2] where PolicyNumber = @PolicyNumber and CheckNumber = @CheckNumber and Amount = @Amount)
BEGIN
INSERT INTO [Employee2](PolicyNumber,CheckNumber,Amount,Collectiondate,NavDate,VendorName)
SELECT
(R.ref.value('PolicyNumber[1]', 'varchar(20)')) as 'PolicyNumber',
R.ref.value('CheckNumber [1]', 'varchar(20)') as ' CheckNumber ',
R.ref.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(ref)
IF(@@ROWCOUNT > 0 )
SET @retValue='SUCCESS';
END
END
my InPut XML:-
Declare @retValue1 varchar(50);
Declare @XmlStr XML;
SET @XmlStr='<tma>
<tma_header>
<conversationid />
<transaction>BIGXML</transaction>
<sourcecountry>India</sourcecountry>
<sourcecompany>MLIN</sourcecompany>
<sourcesystem>METWS</sourcesystem>
<uniquekey>544010844</uniquekey>
</tma_header>
<tma_body>
<branchoperator>
<policynumber>123456</policynumber>
<checknumber>544010844</checknumber>
<amount>17000</amount>
<collectiondate>03/07/2014</collectiondate>
<navdate>03/07/2014</navdate>
<vendorname>Bhadri</vendorname>
</branchoperator>
<branchoperator>
<policynumber>789101</policynumber>
<checknumber>5477866876</checknumber>
<amount>17000</amount>
<collectiondate>04/07/2014</collectiondate>
<navdate>04/07/2014</navdate>
<vendorname>Sandeep</vendorname>
</branchoperator>
<branchoperator>
<policynumber>5678866</policynumber>
<checknumber>54325668786</checknumber>
<amount>17000</amount>
<collectiondate>07/07/2014</collectiondate>
<navdate>07/07/2014</navdate>
<vendorname>Suman</vendorname>
</branchoperator>
</tma_body>
</tma>';
EXEC [SP_XMLtoTABLEInsert] @xmlData=@XmlStr,@retValue=@retValue1 OUTPUT
print @retValue1
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
这篇关于读取XML并使用存储过程插入SQL表如果记录已存在而不是不插入 - 仅通过存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!