SQL: Delete XML node in an UPDATE statement

Question

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.

So your database is SQLServer ? – VBoka Mar 05 '20 at 20:10 — VBoka, Mar 05 '20 at 20:10
Yes, it is. Let me update the question. – JohnPete22 Mar 05 '20 at 20:15 — JohnPete22, Mar 05 '20 at 20:15

score 1 · Answer 1 · answered Mar 05 '20 at 22:14

It comes down to you cannot do SET CustomProperties = CONVERT(xml, CustomProperties).modify(...) because you cannot combine DML and XMLDML operations in the same statement.

In other words, the CustomProperties column has to actually be the xml sql type, not nvarchar(max) or anything else. When that's the case your XML delete statement will succeed:

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

Which yields:

<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>

Although you are right in general (+1 form my side) it is not true that this cannot be done without changing the table's design. See my answer. — Shnugo, Mar 06 '20 at 09:02

score 1 · Accepted Answer · answered Mar 06 '20 at 09:01

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.

Yes, you did tell me already. Unfortunately I cannot change the design. I will give this solution a try this afternoon and report back. As always, thanks for the information in the answer as well. — JohnPete22, Mar 06 '20 at 11:48

SQL: Delete XML node in an UPDATE statement

2 Answers2