------OPENXML和MERGE联合使用的方法--------

在这里我直接拿项目中的代码来说明问题。大家一下就看懂了。

 

Use LvRui
Go
if object_ID('[cuGiftCatagroy]') is not null
	Drop Procedure [cuGiftCatagroy]
Go
/****************************************************************************************************************************************************************
%%存储过程名：cuGiftCatagroy 

%%输入参数：
exec cuGiftCatagroy
'<GiftCatagroy Action="1">
	<ID>16</ID>
	<!--ID-->
	<GiftClassID>1</GiftClassID>	
	<CName>A</CName>
	<Code>1000</Code>
	<Remark>A</Remark>
	<DisplayOrder>1</DisplayOrder>
	<GiftCatagroySpecs Action="1">
	    <GiftCatagroySpec>
		<ID>1</ID>
		<!--ID-->
		<GiftCatagroyID>16</GiftCatagroyID>
		<SpecID>1</SpecID>
		<SpecID>2</SpecID>
		<SpecID>3</SpecID>
		<SpecID>4</SpecID>
		<Mandatory>1</Mandatory>	
		<DisaplayOrder>1</DisaplayOrder>
		</GiftCatagroySpec>
	</GiftCatagroySpecs>
</GiftCatagroy>
',0,0
%%输出参数：

%%功能：新增记录在表Gift
****************************************************************************************************************************************************************
%%编写：fredrickhu  
****************************************************************************************************************************************************************/
CREATE PROCEDURE cuGiftCatagroy 
		(@GiftCatagroy XML,@GiftCatagroyID INT=0 OUTPUT,@GiftCatagroySpecID INT=0 OUTPUT)
AS
SET NOCOUNT ON ;

DECLARE @idoc INT,@NextNr INT,@IsCreate TINYINT,@Code VARCHAR(50)

SELECT @NextNr=NextNr FROM NextSerialNr WHERE SerialNrTypeID=4

SELECT @IsCreate=@GiftCatagroy.exist('GiftCatagroy[@Action=1]')

BEGIN 
IF @IsCreate=1
BEGIN 
	UPDATE NextSerialNr SET NextNr=NextNr+1 WHERE SerialNrTypeID=4
	SET @Code='LP'+RIGHT(100+ROW_NUMBER()OVER(ORDER BY @NextNr)+@NextNr-1,2)
END 
ELSE 
	SET @Code='LP'+RIGHT(100+@NextNr-1,2)
END 

EXEC sp_xml_preparedocument @idoc OUTPUT, @GiftCatagroy


BEGIN TRY
BEGIN TRAN

--GiftCatagroy
BEGIN 
;MERGE GiftCatagroy AS T
USING
(
SELECT
	[GiftClassID],[CName],@Code AS [Code],
	[Remark],[DisplayOrder],Action
FROM OPENXML (@idoc, '@GiftCatagroy[@Action!=0]',3)
WITH 
	(
	[GiftClassID] SMALLINT 'GiftClassID[1]',
	[GiftNr] varchar(50) 'GiftNr[1]',
	[CName] nvarchar(50) 'CName[1]',
	[Code] varchar(50) 'Code[1]',
	[Remark] nvarchar(200) 'Remark[1]',
	[DisplayOrder] smallint 'DisplayOrder[1]',
	Action TINYINT '@Action')
) AS S ON (S.GiftClassID=T.GiftClassID)
WHEN MATCHED AND S.Action=2   THEN 
UPDATE SET /*GiftClassID=S.GiftClassID,*/[CName]=S.CName,[Code]=S.Code ,[Remark]=S.Remark ,[DisplayOrder]=S.DisplayOrder 
WHEN NOT MATCHED AND S.Action=1 THEN
INSERT 
	([GiftClassID],[CName],[Code],[Remark],[DisplayOrder])
VALUES
	(S.GiftClassID,S.CName,S.Code,S.Remark,S.DisplayOrder);

SET @GiftCatagroyID=SCOPE_IDENTITY()

END 


--GiftCatagroySpec
BEGIN 
;MERGE GiftCatagroySpec AS T
USING
(
SELECT 
	[ID],GiftCatagroyID,SpecID,Mandatory,DisplayOrder,Action
FROM OPENXML(@idoc, 'Gift/GiftCatagroySpecs/GiftCatagroySpec[@Action!=0]',3) 
WITH 
	(
	[ID] INT 'ID[1]',
	[GiftCatagroyID] INT '../../ID[1]',
	[SpecID] smallint 'SpecID[1]',
	[Mandatory] BIT 'Mandatory[1]',
	[DisplayOrder] BIT 'DisaplayOrder[1]',
	Action TINYINT '@Action')
) AS S ON S.ID=T.[ID]
WHEN MATCHED AND S.Action=2  THEN
UPDATE SET GiftCatagroyID=S.GiftCatagroyID,SpecID=S.SpecID,Mandatory=S.Mandatory,DisplayOrder=S.DisplayOrder
WHEN NOT MATCHED AND S.Action=1 THEN
INSERT 
	( GiftCatagroyID,SpecID,Mandatory,DisplayOrder)
VALUES  ( S.GiftCatagroyID,S.SpecID,S.Mandatory,S.DisplayOrder);

SET @GiftCatagroySpecID=SCOPE_IDENTITY()

END

 
COMMIT TRAN

END TRY
BEGIN CATCH
	DECLARE @Error NVARCHAR(200)
	SET @Error=ERROR_MESSAGE()
	RAISERROR 50001 @Error
	ROLLBACK TRAN
END CATCH

EXEC sp_xml_removedocument @idoc

 
Go