如何根据 XML 参数更新表

我有一个表，我想根据 XML 参数中的值更新其 varchar 字段之一。

我有下表：

ID  Constraint_Value
1   (OldVal_1) (OldVal_2)
2   (OldVal_2) (OldVal_1)

我想使用以下 XML 来更新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 中运行）：

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保持不变。

如何使用 xml 参数中指定的所有元素更新字段？

利用递归 cte 可以让我得到您正在寻找的结果。如下所示。但至少它不是游标/while循环;)

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