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

问题描述

我正在使用 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>                             ║      ║
╚════════╩══════════════════════════════════════╩══════╝