問題描述

我正在使用 xml 字段以這種格式存儲書籍建議:

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>

在某些時候，我需要在這個 xml 中添加建議.這是通過以下語句完成的:

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

如何確保我沒有插入已經存在的節點(僅基于 id 屬性).

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

推薦答案

實現它的一種方法是創建僅包含新值的新 XML 變量.

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

數據:

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

查詢:

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

╔════════╦══════════════════════════════════════╦══════╗
║ 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>                             ║      ║
╚════════╩══════════════════════════════════════╩══════╝