SQL:在UPDATE语句中删除XML节点

Question

我一直在犯这个错误：

Incorrect use of the XML data type method 'modify'. A non-mutator method is expected in this context.

为了寻找这个答案的解决方案，我看到了一篇关于更新SELECT语句的文章：How do I UPDATE from a SELECT in SQL Server?

例如..。注意:字段CustomProperties是nvarchar(max)，我将其转换为xml。

<CustomProperties
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldA</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldB</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldC</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldD</Key>
        <Value>....</Value>
    </CustomProperty>
</CustomProperties>


DECLARE @myVar varchar(50) = 'FieldA';

UPDATE Table_1
SET 
    Table_1.CustomProperties = CONVERT(xml, Table_2.CustomProperties).modify('delete (/CustomProperties/CustomProperty[Key = sql:variable("@myVar")])')
FROM
    [dbo].MyTable AS Table_1
    INNER JOIN [dbo].MyTable AS Table_2 on Table_1.id = Table_1.id
WHERE
    // shortened for brevity

我也尝试了一个光标(讨厌的东西)，但得到了同样的错误。

有什么方法可以让我对所有行进行全面更新吗？我的目标是为本表中的特定行数据从CustomProperties XML中删除一个节点。

Answer 1

您已经被告知，将XML存储在字符串类型中是一个非常糟糕的主意。在数据设计中使用适当的类型总是最好的选择。

除此之外，如果你必须坚持这个设计(有时我们必须做一些奇怪的事情)，你可能会尝试这样做：

-创建一个模拟表来模拟您的问题：

DECLARE @tbl TABLE(CustomProperties VARCHAR(1000));
INSERT INTO @tbl VALUES
('<CustomProperties
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldA</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldB</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldC</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldD</Key>
        <Value>....</Value>
    </CustomProperty>
</CustomProperties>');

-你的说教绳

DECLARE @myVar varchar(50) = 'FieldA';

--查询

UPDATE @tbl SET CustomProperties = CAST(CAST(CustomProperties AS XML)
                                   .query('
                                            <CustomProperties>
                                            {/CustomProperties/CustomProperty[Key != sql:variable("@myVar")]}
                                            </CustomProperties>
                                         ') AS VARCHAR(1000));
SELECT * FROM @tbl;

简而言之，这个想法：

代替XMLDML (通过.modify())，我们使用一个简单的UPDATE ... SET ...并分配一个重新创建的UPDATE ... SET ...。XML方法.query()将返回一个<CustomProperty>，而不是一个与谓词匹配的<CustomProperty>。

Answer 2

这归结为您不能执行SET CustomProperties = CONVERT(xml, CustomProperties).modify(...)，因为您不能在同一个语句中组合DML和XMLDML操作。

换句话说，CustomProperties列实际上必须是xml sql类型，而不是nvarchar(max)或其他任何类型。在这种情况下，您的XML语句将成功：

select [CustomProperties] = cast('<CustomProperties
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldA</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldB</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldC</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldD</Key>
        <Value>....</Value>
    </CustomProperty>
</CustomProperties>' as xml)
into #tempTable;

declare @myVar varchar(50) = 'FieldA';

update #tempTable
set [CustomProperties].modify('delete (/CustomProperties/CustomProperty[Key = sql:variable("@myVar")])')

select * from #tempTable

产生的结果：

<CustomProperties xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldB</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldC</Key>
        <Value>....</Value>
    </CustomProperty>
    <CustomProperty>
        <Dev>....</Dev>
        <Key>FieldD</Key>
        <Value>....</Value>
    </CustomProperty>
</CustomProperties>