在 SQL Server 的单个 XQuery 中删除多个节点 [英] Deleting Multiple Nodes in Single XQuery for SQL Server

问题描述

我有:

1. 具有 xml 类型列(ID 列表)的表
2. 一个 xml 类型参数(也是 ID 列表)

1. a table with an xml type column (list of IDs)
2. an xml type parameter (also list of IDs)

从与参数中的节点匹配的列中删除节点的最佳方法是什么，同时保留任何未匹配的节点不变?

What is the best way to remove nodes from the column that match the nodes in the parameter, while leaving any unmatched nodes untouched?

例如

declare @table table (
    [column] xml
)

insert @table ([column]) values ('<r><i>1</i><i>2</i><i>3</i></r>')

declare @parameter xml
set @parameter = '<r><i>1</i><i>2</i></r>'

-- this is the problem
update @table set [column].modify('delete (//i *where text() matches @parameter*)')

MSDN 文档表明它应该是可能的(在 SQL Server 中的 XQuery 简介2005):

The MSDN documentation indicates it should be possible (in Introduction to XQuery in SQL Server 2005):

这个存储过程很容易被修改为接受 XML 片段其中包含一项或多项技能元素，从而允许用户删除多个技能节点存储过程的单一调用.

This stored procedure can easily be modified to accept an XML fragment which contains one or more skill elements thereby allowing the user to delete multiple skill nodes with a single invocation of stored procedure.

推荐答案

虽然用这种方式删除有点尴尬，但你可以改用更新来改变数据，前提是你的数据很简单(比如例子你给了).以下查询基本上将两个 XML 字符串拆分成表，连接它们，排除非空(匹配)值，并将其转换回 XML:

While the delete is a little awkward to do this way, you can instead do an update to change the data, provided your data is simple (such as the example you gave). The following query will basically split the two XML strings into tables, join them, exclude the non-null (matching) values, and convert it back to XML:

UPDATE @table 
SET [column] = (
    SELECT p.i.value('.','int') AS c
    FROM [column].nodes('//i') AS p(i)
    OUTER APPLY (
        SELECT x.i.value('.','bigint') AS i
        FROM @parameter.nodes('//i') AS x(i)
        WHERE p.i.value('.','bigint') = x.i.value('.','int')
    ) a
    WHERE a.i IS NULL
    FOR XML PATH(''), TYPE
)

这篇关于在 SQL Server 的单个 XQuery 中删除多个节点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！