本文介绍了在插入 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> ║ ║
╚════════╩══════════════════════════════════════╩══════╝
这篇关于在插入 XML 字段之前过滤重复节点的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!