批量处理sql 数据存入xml类型列

个人记录

需求：当表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