SQL: Delete XML node in an UPDATE statement

I keep getting this error:

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

In search of a solution to this answer I came across this SO article about updating in a SELECT statement: How do I UPDATE from a SELECT in SQL Server?

Example… Note: the field CustomProperties is nvarchar(max) which I convert to 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

I also tried a cursor (nasty things), but getting the same error.

Is there a way where I can do a blanket update of all rows. My goal is to remove one node from the CustomProperties XML for specific row data in this table.

Answer

You were told already, that it is a very bad idea to store XML in a string type. It is always the best choice to use the appropriate type in your data design.

Besides this, if you have to stick to this design (sometimes we must do quirky things), you might try something along this:

–Creating a mockup-table to simulate your issue:

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>');

–your serach string

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

–the query

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

The idea in short:

Instead of XMLDML (via .modify()) we use a simple UPDATE ... SET ... and assign a re-created XML. The XML method .query() will return a XML without the one <CustomProperty> matching the predicate.