T-SQL XML：如果有多个节点具有相同的名称，如何更新一个节点中的值？

Question

我刚刚开始学习如何使用XML数据格式，并且已经在更新一些数据上陷入了困境。我真的，真的很感谢有人帮我解决这个问题，因为我完全不知道怎么处理这样的问题。

生成一些示例数据的代码：

IF OBJECT_iD('tempdb..#beforeXML') is NOT NULL 
    DROP TABLE #beforeXML 

CREATE TABLE #beforeXML 
(
    ID int NOT NULL,    
    SomeXMLData XML NOT NULL
)

INSERT INTO #beforeXML (ID, SomeXMLData)
VALUES 
    (1, '<Parameters><Parameter><Key>ABC</Key><Value>1, 2, 4</Value></Parameter><Parameter><Key>XYZ</Key><Value>A</Value></Parameter></Parameters>'),
    (2, '<Parameters><Parameter><Key>KLM</Key><Value>true</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 4, 5</Value></Parameter></Parameters>'),
    (3, '<Parameters><Parameter><Key>KLM</Key><Value>false</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 6</Value></Parameter><Parameter><Key>XYZ</Key><Value>A, C</Value></Parameter></Parameters>'),
    (4, '<Parameters><Parameter><Key>XYZ</Key><Value>A</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 5</Value></Parameter></Parameters>'),
    (5, '<Parameters><Parameter><Key>XYZ</Key><Value>B</Value></Parameter><Parameter><Key>KLM</Key><Value>true</Value></Parameter></Parameters>')

SELECT * FROM #beforeXML

现在困难的部分来了...

我需要更新“value”节点的值，其中“key”节点的值=“abc”在同一个“parameter”节点中。

正如您所看到的，我的xml中有几个“参数”节点，这些节点没有我可以用来区分它们和确定应该更新哪些节点的特定顺序或属性。有些行没有这样的节点，有些行在“value”节点中已经有了数字3或/和5，所以我只需要在缺少一个或两个（3或/和5）的情况下添加。

我想得到的结果是：

IF OBJECT_iD('tempdb..#afterXML') IS NOT NULL 
    DROP TABLE #afterXML 

CREATE TABLE #afterXML 
(
    ID int NOT NULL,    
    SomeXMLData XML NOT NULL
)

INSERT INTO #afterXML (ID, SomeXMLData)
VALUES 
    -- added both 3, 5
    (1, '<Parameters><Parameter><Key>ABC</Key><Value>1, 2, 3, 4, 5</Value></Parameter><Parameter><Key>XYZ</Key><Value>A</Value></Parameter></Parameters>'),  
    -- added only 3
    (2, '<Parameters><Parameter><Key>KLM</Key><Value>true</Value></Parameter>
<Parameter><Key>ABC</Key><Value>1, 2, 3, 4, 5</Value></Parameter></Parameters>'),    
    -- added only 5
    (3, '<Parameters><Parameter><Key>KLM</Key><Value>false</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 5, 6</Value></Parameter><Parameter><Key>XYZ</Key><Value>A, C</Value></Parameter></Parameters>'),
    -- no change
    (4, '<Parameters><Parameter><Key>XYZ</Key><Value>A</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 5</Value></Parameter></Parameters>'), 
    -- no change
    (5, '<Parameters><Parameter><Key>XYZ</Key><Value>B</Value></Parameter><Parameter><Key>KLM</Key><Value>true</Value></Parameter></Parameters>')  

SELECT * FROM #afterXML

IF OBJECT_iD('tempdb..#temp_table') IS NOT NULL 
    DROP TABLE #temp_table

CREATE TABLE #temp_table 
(
    ID int NOT NULL,    
    NewSetOfValues varchar(100) NOT NULL
)

INSERT INTO #temp_table (ID, NewSetOfValues)
VALUES 
    (1, '1, 2, 3, 4, 5'),
    (2, '1, 2, 3, 4, 5'),
    (3, '1, 2, 3, 5, 6')
 
SELECT * FROM #temp_table

事先谢谢你的帮助。

Answer 1

诚然，这并不是按照数字的顺序对XML字符串进行排序，但您应该能够根据需要对其进行处理：

update b
-- .modify is a special function that modifies XML in place
set SomeXMLData.modify('
    replace value of
    (/Parameters/Parameter[Key[text()="ABC"]]/Value/text())[1]
    with
    concat (
        (/Parameters/Parameter[Key[text()="ABC"]]/Value/text())[1],
        if ((/Parameters/Parameter[Key[text()="ABC"]]/Value[contains(text()[1], "3")])[1] ) then "" else ", 3" ,
        if ((/Parameters/Parameter[Key[text()="ABC"]]/Value[contains(text()[1], "5")])[1] ) then "" else ", 5" 
    )')
from @beforeXML b;

其工作方式如下：

1. 我们搜索XML节点，从根/开始，降序参数，然后降序参数但此节点必须有一个子节点key，该子节点具有text()=“abc”，然后降序/value/text())并取第一个[1]节点。
2. 将此值替换为现有值的串联，并:
3. 如果value节点匹配[contains(text()[1],“3”）])[1])则不匹配，否则添加“,3”
4. 如果value节点匹配[contains(text()[1],“5”）])[1])则不匹配，否则添加“,5”

|SomeDataXML|
----
|<Parameters><Parameter><Key>ABC</Key><Value>1, 2, 4, 3, 5</Value></Parameter><Parameter><Key>XYZ</Key><Value>A</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>KLM</Key><Value>true</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 4, 5, 3</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>KLM</Key><Value>false</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 6, 5</Value></Parameter><Parameter><Key>XYZ</Key><Value>A, C</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>XYZ</Key><Value>A</Value></Parameter><Parameter><Key>ABC</Key><Value>1, 2, 3, 5</Value></Parameter></Parameters>|
|<Parameters><Parameter><Key>XYZ</Key><Value>B</Value></Parameter><Parameter><Key>KLM</Key><Value>true</Value></Parameter></Parameters>|