Update XML data type without creating duplicate nodes

Question

In my application, users can select groups of skills to search, which are stored as XML. In this simple example, I have the following structure in a table with a primary XML index included.

CREATE TABLE #myTable (ID int IDENTITY(1,1) PRIMARY KEY, userId int NOT NULL, searchXml xml not null);
GO
CREATE PRIMARY XML INDEX [Ix1] ON #myTable (searchXml) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
INSERT INTO #myTable (userId, searchXml) VALUES 
    (1, '<search><skills><s id="4" /><s id="10" /></skills></search>'),
    (1, '<search><skills><s id="4" /><s id="8" /><s id="10" /></skills></search>'),
    (2, '<search><skills><s id="7" /><s id="8" /><s id="10" /></skills></search>'),
    (3, '<search><skills><s id="4" /><s id="10" /><s id="11" /></skills></search>');
SELECT * FROM #myTable;


DROP TABLE #myTable;
GO

I need to perform the following update, but after some lengthy reading up cannot get anything even close:

Skill ID 4 is to be combined with Skill ID 8, leaving only Skill ID 8. Therefore, each piece of XML data needs to be updated in the following manner:

1. If 4 & 8 exist in the same XML, remove 4, leaving only 8
2. If 4 exists in isolation, then change 4 to 8
3. If 8 exists in isolation, take no action

Can anyone help me improve my XML skills by sharing a solution?

Answer 1

I can do it with two updates:

1. If 4 & 8 exist in the same XML, remove 4, leaving only 8

UPDATE #myTable
SET searchXml.modify('
delete (/search/skills[data(s/@id)=4 and data(s/@id)=8]/s[@id=4])')

and if only one occurence of <s id="4"/> is allowed per skills element and only one skills element per search and only one search element:

2. If 4 exists in isolation, then change 4 to 8

UPDATE #myTable
SET searchXml.modify('
replace value of 
(/search/skills[data(s/@id)=4 and not(data(s/@id)=8)]/s[@id=4]/@id)[1] 
 with 8 ')