MSSQL XML字段类型的查询和批量替换 XML属性值

SELECT
Id AS Id
,[Description].value('(items[1]/item[@culture="zh-cn"]/@value)[1]','varchar(max)') AS ZNVal

,[Description].query('//item[@culture="en-us"]').value('(/item/@value)[1]','varchar(max)')) AS Descriptions
,[Description] AS Desp
INTO #Temp
FROM DCL.SystemCodeType

DECLARE @xmlField XML;
DECLARE @znValue VARCHAR(max)
DECLARE @Id INT
DECLARE @rowCount INT
Declare @tranError INT
Set @tranError = 0
SELECT @rowCount = COUNT(*) FROM #Temp

BEGIN TRANSACTION
WHILE @rowCount > 0
BEGIN
SELECT @Id=Id,@znValue=ZNVal,@xmlField=Desp FROM #Temp WHERE Id = @rowCount
SET @xmlField.modify('replace value of (items[1]/item[@culture="en-us"]/@value)[1] with sql:variable("@znValue")')
UPDATE DCL.SystemCodeType SET [Description] = @xmlField WHERE Id = @Id

SET @rowCount = @rowCount - 1
END
Set @tranError = @tranError + @@Error
IF @tranError = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
DROP TABLE #Temp
GO

 

转载于:https://www.cnblogs.com/jake-hl/p/7346985.html