T-SQL XML : how to update value in one node if there are more nodes with identical names?

Question

I'm just starting to learn how to work with XML data format and I've already got stuck on updating some data. I would really, really appreciate some help with the issue 'cause i have absolutely no idea how to deal with such problem.

Code to generate some sample data:

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

Now comes the hard part...

I need to update "Value" node's value where "Key" node's value = "ABC" within the same "Parameter" node.

As you can see I have several "Parameter" nodes inside my xml, these nodes don't have specific order or attributes I could use to distinguish them and determine which ones should I update. There are some rows that don't have such node, and there are some rows that already have digits 3 or/and 5 inside "Value" node, so I need to add only in case one or both (3 or/and 5) is missing.

The result I would like to obtain:

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

I've managed to extract values from that particular "Value" node for each row, check what digits are missing and prepare data for update

So I have temp_table with data like this:

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

but that's where I've got stuck.

I have absolutely no idea how to construct proper modify method syntax to update only specific "Value" node within presented xml structure... :(

Is there some easy way to deal with such update?

Thank you in advance for any help.

Answer 1

Admittedly, this does not sort the XML string in order of number, but you should be able to work it in to how you need it:

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;

The way this works is as follows:

1. We search for XML nodes, starting at root /, descending Parameters, then Parameter but this node must have a sub-node Key which has a text()="ABC", then descend /Value/text()) and take the first [1] node.
2. Replace this value with the concatenation of the existing value, and:
3. if Value node matches [contains(text()[1], "3")])[1] ) then nothing, else we add ", 3"
4. if Value node matches [contains(text()[1], "5")])[1] ) then nothing, else we add ", 5"

Result:

|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>|