个人记录
需求：当表T1 ItemCode和表T2 ItemName的数据相等时，将表T2所对应的ID和ItemName列的数据分别存入表T1 CAOZUO字段的id元素和text元素的文本中。
下面用存储过程循环来实现批量处理sql 数据存入xml类型数据：
CREATE PROCEDURE [dbo].[PRE_XMCus] AS
BEGIN
--创建临时表
CREATE TABLE #TEMPS (Dspid uniqueidentifier NULL,ItemCode VARCHAR(100) NULL,Id uniqueidentifier NULL,ItemName NVARCHAR(100) NULL,ROWID INT NULL)
DECLARE @BatchID uniqueidentifier
SET @BatchID =NEWID() INSERT INTO #TEMPS
(Dspid,ItemCode,Id,ItemName,ROWID)
SELECT @BatchID AS Dspid,T1.ItemCode,T2.Id,T2.ItemName,ROW_NUMBER() OVER(ORDER BY T1.ItemCode)AS ROWID
FROM TECUL_XMCus T1
INNER JOIN Tecul_SysUsers T2 ON T1.ItemCode=T2.ItemName AND T2.IsDelete=0
WHERE T1.IsDelete=0 AND T1.CAOZUO IS NULL
--先将操作用户字段为NULL的赋值
UPDATE T2
SET T2.CAOZUO='<CaoZuo><data><item><id>64b2e004-1038-415b-8522-7c1a8c974572</id><text>020001</text></item></data></CaoZuo>'
FROM TECUL_XMCus T2
INNER JOIN Tecul_SysUsers T3 ON T2.ItemCode=T3.ItemName AND T3.IsDelete=0
WHERE T2.IsDelete=0 AND T2.CAOZUO IS NULL
--获取最大行数
DECLARE @Maxrow INT=(SELECT COUNT(*) FROM #TEMPS)
--开始循环
WHILE(@Maxrow>0)
BEGIN
BEGIN TRY
BEGIN TRAN
IF EXISTS(SELECT 1 FROM #TEMPS WHERE Dspid=@BatchID AND ROWID=@Maxrow)
BEGIN
DECLARE @ItemCode VARCHAR(20)
SELECT @ItemCode=ItemCode FROM #TEMPS WHERE Dspid=@BatchID AND ROWID=@Maxrow
IF EXISTS(SELECT 1 FROM #TEMPS WHERE Dspid=@BatchID AND ROWID=@Maxrow)
BEGIN
DECLARE @aid uniqueidentifier,@bid NVARCHAR(100)
SET @aid=(SELECT Id FROM #TEMPS WHERE ItemCode=@ItemCode AND Dspid=@BatchID AND ROWID=@Maxrow)
set @bid=(SELECT ItemName FROM #TEMPS WHERE ItemCode=@ItemCode AND Dspid=@BatchID AND ROWID=@Maxrow)
--更新id元素文本
UPDATE T2
SET CAOZUO.modify('replace value of (/CaoZuo/data/item/id/text())[1] with sql:variable("@aid")')
FROM #TEMPS T1
JOIN TECUL_XMCus T2 ON T1.ItemCode=T2.ItemCode AND T2.IsDelete=0
WHERE T2.ItemCode=@ItemCode AND Dspid=@BatchID AND ROWID=@Maxrow
--更新text元素文本
UPDATE T2
SET CAOZUO.modify('replace value of (/CaoZuo/data/item/text/text())[1] with sql:variable("@bid")')
FROM #TEMPS T1
JOIN TECUL_XMCus T2 ON T1.ItemCode=T2.ItemCode AND T2.IsDelete=0
WHERE T2.ItemCode=@ItemCode AND Dspid=@BatchID AND ROWID=@Maxrow
END
END
COMMIT TRAN
END TRY
BEGIN CATCH
DECLARE @ERRORMES NVARCHAR(300)
SET @ERRORMES=ERROR_MESSAGE()
ROLLBACK TRAN
END CATCH
SET @Maxrow=@Maxrow-1
END -- SELECT * FROM #TEMPS
-- DROP TABLE #TEMPS
END