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

score 4 · Accepted Answer · edited May 23 '17 at 10:28

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.

I could relax requirements,so go on and show dynamic-sql :) Anyway I will offer bounty after 2 days and accept it after few days. — Lukasz Szozda, Jan 29 '16 at 13:05
@lad2025 even dynamically it would need two times modify(). And no chance for ad-hoc sql. Not an option actually ... — Shnugo, Jan 29 '16 at 13:09

score 0 · Answer 2 · answered Feb 01 '16 at 09:12

Try something like this,it is easy to understand ,but it is lengthy.

Let me know,if you have any problem.

declare @Users TABLE(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>';

declare @Users1 TABLE(userid INT , productid int,score int);
insert into @Users1 
SELECT id userid,
    t.c.value('(@id[1])', 'VARCHAR(50)') AS Productid,
    t.c.value('(@score[1])', 'VARCHAR(50)') AS score

FROM @Users yt
    cross APPLY yt.suggestions.nodes('Products/Product') t(c)
--select * from @Users1
;With CTE1 as
(
    select @userId userid,
    t.c.value('(@id[1])', 'VARCHAR(50)') AS Productid,
    t.c.value('(@score[1])', 'VARCHAR(50)') AS score
   from @suggestions.nodes('Products/Product') t(c)
)

Merge @users1 as trg
using cte1 as src
 on trg.userid=src.userid and trg.productid=src.productid
when not matched then
insert (userid,productid,score) values(src.userid,src.productid,src.score);

select distinct a.userid
 ,(select b.productid as '@Productid',b.score as '@Score'  
from @users1 b where a.userid=b.userid  
for xml path('Product'),root('Products'))  
from @users1 a

Thanks for reply but I know answer that utilize `.nodes()/.values()` **(see remarks)**. The whole point of this question is to get the result using only `.modify()`. And check **[demo](https://data.stackexchange.com/stackoverflow/query/430681)**. 1) Your answer does not update `suggestions` column 2) It does not handle `Product id = 3` correctly (see that it has score 4 not 2). — Lukasz Szozda, Feb 01 '16 at 09:16

score 0 · Answer 3 · answered Feb 03 '16 at 00:55

This is not the elegant one-liner you were hoping for. Nevertheless, it kind of works, so I will share. (And I'm sure there's room for improvement.)

    IF OBJECT_ID('tempdb..#Users') IS NOT NULL DROP TABLE #Users

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

    --@Suggestions is slightly different (note the "<Products>" parent element is gone).
    DECLARE @userId INT = 1,
            @suggestions XML = N' <Product id="2" score="5"/>
                                  <Product id="3" score="2"/>
                                  <Product id="7" score="1" />';

    --Capture the original suggestions so we can compare later
    DECLARE @OldXML As XML = (SELECT suggestions from #Users where id = @userId)

    UPDATE #Users   --this inserts the new suggestions (we delete old ones as needed, below).
    SET suggestions.modify('insert sql:variable("@suggestions") as first into /Products[1]')
    WHERE id = @userId;

    WHILE (@OldXML.exist('/Products/Product') = 1)
      BEGIN  --For each of the user's original Products, delete the second instance of it.
        DECLARE @prodId int = (SELECT @OldXML.value('(/Products/Product/@id)[1]', 'nvarchar(max)') FROM #Users where id = @userId)
        UPDATE #Users SET suggestions.modify('delete /Products/Product[@id= sql:variable("@prodId")][2]') WHERE id = @userId
        SET @OldXML.modify('delete /Products/Product[@id=sql:variable("@prodId")][1]')
      END

    SELECT * FROM #Users where id = @userId
    DROP TABLE #Users

The reason for this question was this: http://stackoverflow.com/q/35046529/5089204. The OP himself wrote the accepted answer there. Have a look at his answer and at my answer there too and at the comments... There are much better ways than the one you show in this answer (btw: **no loops!* if avoidable). The question here is: One call to `.modify()` doing a conditional UPSERT in one go. As I've pointed out, I think this is not possible... — Shnugo, Feb 03 '16 at 12:58

shA.t · Answer 4 · 2016-02-07T09:42:22.340

I think you can use a query like this:

UPDATE #Users
SET suggestions = (
    SELECT id, score
    FROM
        (SELECT 
            *,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY ord) As seq
        FROM (
            SELECT 
                c.value('@id', 'INT') AS id,
                c.value('@score', 'INT') AS score,
                1 As ord
            FROM
                @suggestions.nodes('/Products/Product') As t(c)
            UNION ALL
            SELECT 
                c.value('@id', 'INT') AS id,
                c.value('@score', 'INT') AS score,
                2 as ord
            FROM
                (SELECT suggestions x FROM #Users WHERE id = @userId) As u CROSS APPLY
                u.x.nodes('/Products/Product') As t(c)) dt) Product
    WHERE seq = 1
    FOR XML AUTO, ROOT('Products'))
WHERE
    (id = @userId);

SELECT * 
FROM #Users;

[Playground]

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

4 Answers4

Linked