Filter duplicate nodes before inserting into XML field

Question

I am using an xml field to store suggestions for books in this format:

<Books>
  <Book id="1" score="2" />
  <Book id="2" score="3" />
</Books>

At some point I need to add suggestions into this xml. This is done with these statements:

DECLARE @books XML;
SELECT @books = Suggestions.query('//books/book') 
FROM User
WHERE UserId = @UserId

UPDATE User
SET Suggestions.modify('insert sql:variable("@books") as first into (//books)[1]')
WHERE UserId = @UserId

How can I make sure I'm not inserting nodes that already exist (based on the id attribute only).

Answer 1

One way to achieve it could be creating new XML variable with only new values.

Data:

CREATE TABLE #User(UserId INT, Suggestions XML, Name VARCHAR(100));

INSERT INTO #User(UserId, Suggestions, Name)
VALUES (1, 
       '<Books>
          <Book id="1" score="2" />
          <Book id="2" score="3" />
        </Books>'
        ,'John');

DECLARE @books XML =N'<Book id="1" score="2" />
                      <Book id="2" score="3" />
                      <Book id="3" score="4" />
                      <Book id="4" score="4" />';

Query:

DECLARE @UserId INT = 1;
       ,@only_new_books XML;

;WITH books AS
(
   SELECT id    = s.c.value('@id', 'INT'),
          score = s.c.value('@score', 'INT')
   FROM @books.nodes('/Book') AS s(c)
), suggestions AS
( 
  SELECT UserId, 
      id    = s.c.value('@id', 'INT'),
      score = s.c.value('@score', 'INT')
  FROM #User
  CROSS APPLY Suggestions.nodes('//Books/Book') AS s(c)
  WHERE UserId = @UserId
)
SELECT @only_new_books = (SELECT b.id AS '@id',
                                 b.score AS '@score'
                          FROM books b
                          LEFT JOIN suggestions s
                            ON b.id = s.id
                          WHERE s.id IS NULL
                          FOR XML PATH('Book'),TYPE
                          );

UPDATE #User
SET Suggestions.modify('insert sql:variable("@only_new_books") 
                        as first into (//Books)[1]')
WHERE UserId = @UserId;

SELECT * FROM #User;

LiveDemo

Output:

╔════════╦══════════════════════════════════════╦══════╗
║ UserId ║             Suggestions              ║ Name ║
╠════════╬══════════════════════════════════════╬══════╣
║      1 ║ <Books>                              ║ John ║    
║        ║    <Book id="3" score="4" />         ║      ║
║        ║    <Book id="4" score="4" />         ║      ║
║        ║    <Book id="1" score="2" />         ║      ║
║        ║    <Book id="2" score="3" />         ║      ║
║        ║ </Books>                             ║      ║
╚════════╩══════════════════════════════════════╩══════╝

Answer 2

If you really want nothing else than a check before you insert read this

Just change your code to

DECLARE @id INT = @books.value('(/Book/@id)[1]','int'); --the book-id of the "new" suggestion

UPDATE User
SET Suggestions.modify('insert sql:variable("@books") as first into (//Books)[1]')
WHERE UserId = @UserId
  AND Suggestions.exist('//Book[@id=sql:variable("@id")]')=0

If you want to "merge" two Books-structures read this

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

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

WITH Xml1 AS
(
    SELECT One.Book.value('@id','int') AS id
          ,One.Book.value('@score','int') AS score
    FROM @xml1.nodes('/Books/Book') AS One(Book)
)
,Xml2 AS
(
    SELECT One.Book.value('@id','int') AS id
          ,One.Book.value('@score','int') AS score
    FROM @xml2.nodes('/Books/Book') AS One(Book)
)
SELECT ISNULL(Xml2.id,Xml1.id) AS [@id]
      ,ISNULL(Xml2.score,Xml1.score) AS [@score]
FROM Xml1
FULL OUTER JOIN Xml2 ON Xml1.id=Xml2.id
ORDER BY ISNULL(Xml2.id,Xml1.id)
FOR XML PATH('Book'),ROOT('Books');

/*
<Books>
  <Book id="1" score="3" /> --score changed
  <Book id="2" score="3" /> --unchanged
  <Book id="4" score="4" /> --remained
  <Book id="5" score="5" /> --new
</Books>
*/

You might pack this into an UDF and use it like

UPDATE User 
SET Suggestions=dbo.Function(Suggestions,@NewSuggestions)
WHERE UserID=@userID

If you want to insert new and change existing read this

Regrettfully the MERGE statement is not allowed here. You'd need an UPDATE command in the MATCHED and as well as in the NOT MATCHED section...

This is my suggestion:

CREATE TABLE #User (UserID INT,Suggestions XML);
INSERT INTO #User VALUES
 (1,
'<Books>
  <Book id="1" score="2" />
  <Book id="2" score="3" />
</Books>');
GO

CREATE PROCEDURE dbo.UpdateOrInsertSuggestion(@UserID INT, @id INT, @score INT)
AS
BEGIN
    DECLARE @XmlNode XML=(SELECT @id AS [@id],@score AS [@score] FOR XML PATH('Book'));

    DECLARE @UsersSuggestions XML=
    (
        SELECT Suggestions
        FROM #User
        WHERE UserID=@UserID
    );

    IF @UsersSuggestions.exist('//Book[@id=sql:variable("@id")]')=1
        SET @UsersSuggestions.modify('replace value of (//Book[@id=sql:variable("@id")]/@score)[1] with sql:variable("@score")');
    ELSE
        SET @UsersSuggestions.modify('insert sql:variable("@XmlNode") as first into (//Books)[1]');

    UPDATE #User SET Suggestions=@UsersSuggestions 
    WHERE UserID=@UserID;
END
GO

EXEC dbo.UpdateOrInsertSuggestion 1,2,2; --change score to 2 on id=2
SELECT * FROM #User;

/*
<Books>
  <Book id="1" score="2" />
  <Book id="2" score="2" />
</Books>
*/

EXEC dbo.UpdateOrInsertSuggestion 1,5,5; --insert new
SELECT * FROM #User;

/*
<Books>
  <Book id="5" score="5" />
  <Book id="1" score="2" />
  <Book id="2" score="2" />
</Books>
*/
GO


DROP PROCEDURE dbo.UpdateOrInsertSuggestion;
DROP TABLE #User;
GO