问题描述
我有一个表,我想根据 XML 参数中的值更新其中一个 varchar 字段.
I have a table and I want to update one of its varchar fields based on the values in an XML parameter.
我有下表:
ID Constraint_Value 1 (OldVal_1) (OldVal_2) 2 (OldVal_2) (OldVal_1)
并且我想使用以下 XML 来更新 Constraint_Value 字段:
and I want to use the following XML to update the Constraint_Value field:
<qaUpdates> <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate> <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate> </qaUpdates>
更新后,我的目标是:
ID Constraint_Value 1 (NewVal_1) (NewVal_2) 2 (NewVal_2) (NewVal_1)
以下 SQL 说明了我的问题(无需任何设置即可在 SQL Management Studio 中运行):
The following SQL illustrates my problem (which you can run in SQL Management Studio without any set up) :
IF OBJECT_ID('tempdb..#tmpConstraint') IS NOT NULL DROP TABLE #tmpConstraint GO CREATE TABLE tempdb..#tmpConstraint ( constraint_id INT PRIMARY KEY, constraint_value varchar(256) ) GO insert into #tmpConstraint values (1, '(OldVal_1) (OldVal_2)') insert into #tmpConstraint values (2, '(OldVal_2) (OldVal_1)') select * from #tmpConstraint declare @myXML XML set @myXML = N'<qaUpdates> <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate> <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate> </qaUpdates>' update c set constraint_value = REPLACE(constraint_value, Child.value('(old)[1]', 'varchar(50)'), Child.value('(new)[1]', 'varchar(50)')) from #tmpConstraint c cross join @myXML.nodes('/qaUpdates/qaUpdate') as N(Child) select * from #tmpConstraint
结果如下:
(Before) 1 (OldVal_1) (OldVal_2) 2 (OldVal_2) (OldVal_1) (After) 1 (NewVal_1) (OldVal_2) 2 (OldVal_2) (NewVal_1)
如您所见,只有 OldVal_1 已更新.OldVal_2 保持不变.
As you can see just OldVal_1 has been updated. OldVal_2 has remained the same.
如何使用 xml 参数中指定的所有元素更新字段?
推荐答案
使用递归 cte 使我能够获得您正在寻找的结果.如下图所示.但至少它不是游标/while 循环 ;)
Making use of a recursive cte allows me to get the result you're looking for. As the following shows. But att least its not a cursor/while-loop ;)
declare @tmpConstraint table (ID int , Constraint_Value varchar(256)) insert into @tmpConstraint values (1, '(OldVal_1) (OldVal_2)'), (2, '(OldVal_2) (OldVal_1)') declare @myXML XML set @myXML = N'<qaUpdates> <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate> <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate> </qaUpdates>' declare @xmlData table (oldValue varchar(256), newValue varchar(256)) insert into @xmlData select oldValue = Child.value('(old)[1]', 'varchar(50)'), newValue = Child.value('(new)[1]', 'varchar(50)') from @myXML.nodes('/qaUpdates/qaUpdate') as N(Child)
以上只是为以下设置.
;with cte (ID, Constraint_Value, CLevel) as ( select c.ID, c.Constraint_Value, 1 from @tmpConstraint c union all select p.ID, cast(replace(p.Constraint_Value, x.oldValue, x.newValue) as varchar(256)), p.CLevel + 1 from cte p join @xmlData x on p.Constraint_Value like '%' + x.oldValue + '%' ) update c set c.Constraint_Value = t.Constraint_Value from @tmpConstraint c join ( select *, rn = row_number() over (partition by ID order by CLevel desc) from cte ) t on t.ID = c.ID and rn = 1 select * from @tmpConstraint