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

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

 

Use LvRuiGoif object_ID('[cuGiftCatagroy]') is not nullDrop 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)ASSET NOCOUNT ON ;DECLARE @idoc INT,@NextNr INT,@IsCreate TINYINT,@Code VARCHAR(50)SELECT @NextNr=NextNr FROM NextSerialNr WHERE SerialNrTypeID=4SELECT @IsCreate=@GiftCatagroy.exist('GiftCatagroy[@Action=1]')BEGIN IF @IsCreate=1BEGIN UPDATE NextSerialNr SET NextNr=NextNr+1 WHERE SerialNrTypeID=4SET @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, @GiftCatagroyBEGIN TRYBEGIN TRAN--GiftCatagroyBEGIN ;MERGE GiftCatagroy AS TUSING(SELECT[GiftClassID],[CName],@Code AS [Code],[Remark],[DisplayOrder],ActionFROM 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 THENINSERT ([GiftClassID],[CName],[Code],[Remark],[DisplayOrder])VALUES(S.GiftClassID,S.CName,S.Code,S.Remark,S.DisplayOrder);SET @GiftCatagroyID=SCOPE_IDENTITY()END --GiftCatagroySpecBEGIN ;MERGE GiftCatagroySpec AS TUSING(SELECT [ID],GiftCatagroyID,SpecID,Mandatory,DisplayOrder,ActionFROM 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  THENUPDATE SET GiftCatagroyID=S.GiftCatagroyID,SpecID=S.SpecID,Mandatory=S.Mandatory,DisplayOrder=S.DisplayOrderWHEN NOT MATCHED AND S.Action=1 THENINSERT ( GiftCatagroyID,SpecID,Mandatory,DisplayOrder)VALUES  ( S.GiftCatagroyID,S.SpecID,S.Mandatory,S.DisplayOrder);SET @GiftCatagroySpecID=SCOPE_IDENTITY()END COMMIT TRANEND TRYBEGIN CATCHDECLARE @Error NVARCHAR(200)SET @Error=ERROR_MESSAGE()RAISERROR 50001 @ErrorROLLBACK TRANEND CATCHEXEC sp_xml_removedocument @idoc Go