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

suppose have:

create table #users(id int primary key, name varchar(100), suggestions xml);  insert #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 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 want merge 2 xmls based on id attribute:

final result user 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 2   <product id="7" score="1"/> -- insert new element </products> 

please note not combining 2 xmls "upsert" operation.

remarks:

• i know kind of schema violates database normalization , normalizing way go (but not in case)
• i know solution utilize derived tables, .nodes() , .value() functions first parse both xml, merge , write back

i searching xpath/xquery expression merge in 1 statement (no derived tables/dynamic-sql*):

^{* if absolutely needed, dynamic sql used, want avoid it.}

update #users set suggestions.modify(... sql:variable("@suggestions") ...); --changes here id = @userid;   /* replace ... ... ... sql:variable */ 

after trying around while think not possible...

there similar question here: xquery adding or replacing attribute in single sql update command

the .modify(insert expression1 ... ) not allow data within xml passed in via @sql:variable() or sql:column()

read here: https://msdn.microsoft.com/en-us/library/ms175466.aspx @ expression1 -> "constant xml or stand alone sql:column / sql:variable or xquery (to 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 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") first /products[1]');  select @xml1;  /* full node inserted! without kind of preparation there no chance inner nodes  <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 second xml such:

declare @xml2 xml= --the xml new or changed data '<product id="2" score="5" />  <product id="3" score="2" />  <product id="7" score="1" />'; 

but you'll have no chance use id's value xquery filter

set @xml1.modify('insert sql:variable("@xml2") first /products[**how should 1 filter here?**]'); 

and last not least think there no chance combine 2 different xml_dml statements within 1 call of .modify().

the idea had this, doesn't work. if seems usable within expression, not 2 distinguish between 2 execution paths

set @xml1.modify('if (1=1)                      insert sql:variable("@xml2") first /products[1]                   else                      replace value of /products[1]/product[@id=1][1]/@score 100'); 

so conclusion: no, not possible...

the solution provided here https://stackoverflow.com/a/35060150/5089204 in second section ("if want 'merge' 2 books-structures") way solve this.