SQL Server - merge two XML using only .modify()

Question

Suppose we have:

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

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

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

Playground

Now I want to merge 2 XMLs based on id attribute:

Final result for user with id = 1:

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

Please note that it is not combining 2 XMLs but "upsert" operation.

Remarks:

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

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

^{* If absolutely needed, Dynamic SQL could be used, but I want to avoid it.}

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


/* replace ... for ... where ... with sql:variable */

Answer 1

After trying around a while I think this is not possible...

There is similar question here: XQuery adding or replacing attribute in single SQL update command

The .modify(insert Expression1 ... ) does not allow to get data within an XML passed in via @sql:variable() or sql:column()

Read here: https://msdn.microsoft.com/en-us/library/ms175466.aspx at Expression1 -> "constant XML or stand alone sql:column / sql:variable or XQuery (to the same instance)

DECLARE @xml1 XML= --the existing XML
'<Products>
  <Product id="1" score="1" />
  <Product id="2" score="5" />
  <Product id="3" score="4" />
</Products>';

DECLARE @xml2 XML= --the XML with new or changed data
'<Products>
  <Product id="2" score="5" />
  <Product id="3" score="2" />
  <Product id="7" score="1" />
</Products>';

SET @xml1.modify('insert sql:variable("@xml2") as first into /Products[1]');

SELECT @xml1;

/* The full node is inserted!
Without any kind of preparation there is NO CHANCE to get the inner nodes only

<Products>
  <Products>
    <Product id="2" score="5" />
    <Product id="3" score="2" />
    <Product id="7" score="1" />
  </Products>
  <Product id="1" score="1" />
  <Product id="2" score="5" />
  <Product id="3" score="4" />
</Products>
*/

You might declare the second XML as such:

DECLARE @xml2 XML= --the XML with new or changed data
'<Product id="2" score="5" />
 <Product id="3" score="2" />
 <Product id="7" score="1" />';

But than you'll have no chance to use the id's value as XQuery filter

SET @xml1.modify('insert sql:variable("@xml2") as first into /Products[**How should one filter here?**]');

And last but not least I think there is no chance to combine two different XML_DML statements within one call of .modify().

The only idea I had was this, but it doesn't work. IF seems to be usable only within an Expression, but not two distinguish between two execution paths

SET @xml1.modify('if (1=1) then
                     insert sql:variable("@xml2") as first into /Products[1]
                  else
                     replace value of /Products[1]/Product[@id=1][1]/@score with 100');

So my conclusion: No, this is not possible...

The solution I provided here https://stackoverflow.com/a/35060150/5089204 in the second section ("If you want to 'merge' two Books-structures") would be my way to solve this.