sql-server - 在插入 XML 字段之前过滤重复节点

Question

我正在使用 xml 字段以这种格式存储对书籍的建议:

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

有时我需要将建议添加到此 xml 中。这是通过以下语句完成的:

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 属性)。

Answer 1

实现它的一种方法是创建仅包含新值的新 XML 变量。

数据:

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