如何根据 XML 参数更新表 [英] How to update a table based on an XML parameter

问题描述

我有一个表，我想根据 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

这篇关于如何根据 XML 参数更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！