SQL Server - merge two XML using only .modify()
<p>Suppose we have:</p>

<pre><code>CREATE TABLE #Users(id INT PRIMARY KEY, name VARCHAR(100), suggestions XML);

INSERT INTO #Users(id, name, suggestions)
SELECT 1, 'Bob', N'&lt;Products&gt;
                     &lt;Product id="1" score="1"/&gt;
                     &lt;Product id="2" score="5"/&gt;
                     &lt;Product id="3" score="4"/&gt;
                   &lt;/Products&gt;'
UNION ALL
SELECT 2, 'Jimmy', N'&lt;Products&gt;
                       &lt;Product id="6" score="3"/&gt;
                     &lt;/Products&gt;';

DECLARE @userId INT = 1,
        @suggestions XML = N'&lt;Products&gt;
                              &lt;Product id="2" score="5"/&gt;
                              &lt;Product id="3" score="2"/&gt;
                              &lt;Product id="7" score="1" /&gt;
                             &lt;/Products&gt;';
</code></pre>

<p><kbd><strong><a href="https://data.stackexchange.com/stackoverflow/query/429347" rel="nofollow"><code>Playground</code></a></strong></kbd></p>

<p>Now I want to merge 2 XMLs  based on <code>id</code> attribute:</p>

<p>Final result for user with id = 1:</p>

<pre><code>&lt;Products&gt;
  &lt;Product id="1" score="1"/&gt; -- nothing changed (but not exists in @suggestions)
  &lt;Product id="2" score="5"/&gt; -- nothing changed (but exists in @suggestions)
  &lt;Product id="3" score="2"/&gt; -- update score to 2
  &lt;Product id="7" score="1"/&gt; -- insert new element
&lt;/Products&gt;
</code></pre>

<p>Please note that it is not combining 2 XMLs but "upsert" operation.</p>

<p><strong>Remarks:</strong></p>

<ul>
<li>I know that this kind of schema violates database normalization and normalizing it is the way to go (but not in this case)</li>
<li>I know solution that utilize derived tables, <code>.nodes()</code> and <code>.value()</code> functions first to parse both XML, then merge and write back</li>
</ul>

<p><strong>I am searching for is <code>XPath/XQuery</code> expression that will merge it in one statement (no derived tables/dynamic-sql*):</strong></p>

<p><sup>* If absolutely needed, Dynamic SQL could be used, but I want to avoid it.</sup></p>

<pre><code>UPDATE #Users
SET suggestions.modify(... sql:variable("@suggestions") ...); --changes only here
WHERE id = @userId;


/* replace ... for ... where ... with sql:variable */
</code></pre>