Update multiple attributes on sql server xml data field at once

Question

Is it possible to modify multiple attributes on a single node within one Update?

I have something similar to:

<ENTITY NAME="entity1">
    <ATTR ID="attr1" CAPTION="Attributes to Change" SIZE="100" WIDTH="100"></ATTR>
</ENTITY>

I've been able to use three separate Updates to modify the CAPTION, SIZE, and WIDTH attributes, but I've looked in vain for some way to combine them into a single Update. Is it possible?

Examples of the Updates I've used:

UPDATE    t
SET       [column_name].modify('replace value of (/ENTITY/ATTR/@CAPTION)[1] with "New Attribute Caption"')
FROM      table t

UPDATE    t
SET       [column_name].modify('replace value of (/ENTITY/ATTR/@SIZE)[1] with "200"')
FROM      table t

UPDATE    t
SET       [column_name].modify('replace value of (/ENTITY/ATTR/@WIDTH)[1] with "200"')
FROM      table t

No, as far as I know, there's no way to combine multiple updates to a XML column into a single statement. You have what works - leave it that way. — marc_s, Aug 09 '12 at 19:37
Possible duplicate of [TSQL 2005, XML DML - Update Two Values at once?](http://stackoverflow.com/questions/7327440/tsql-2005-xml-dml-update-two-values-at-once) — Yishai, Aug 11 '16 at 02:53

score 3 · Accepted Answer · answered Aug 09 '12 at 19:47

3

No it is not possible.

From replace value of (XML DML).

Expression1
Identifies a node whose value is to be updated. It must identify only a single node. That is, Expression1 must be a statical singleton.

answered Aug 09 '12 at 19:47

Mikael Eriksson

136,425
22
210
281

score 2 · Answer 2 · answered Aug 13 '12 at 15:13

You do have a couple of options but may not be much better off than your three updates. Options are 1) reconstruct the XML or 2) create a mapping table.

DML operations on XML are expensive so there is a tipping point when reconstructing the XML may be more efficient - depending on the size of your XML, how many attributes are being replaced etc

The mapping table solution uses a loop so is similar to the three updates solution. However you do have the advantage of making it data-driven.

Anyway, have a look through this demo, let me know how you get on:

USE tempdb
GO
SET NOCOUNT ON

DECLARE @t TABLE ( yourXML XML )

INSERT INTO @t ( yourXML )
SELECT '<ENTITY NAME="entity1"> 
    <ATTR ID="attr1" CAPTION="Attributes to Change" SIZE="100" WIDTH="100"></ATTR> 
</ENTITY>'


SELECT 'before' s, * FROM @t

------------------------------------------------------------------------------------------------
-- Reconstruct the inner xml START
------------------------------------------------------------------------------------------------

-- Delete current element and attributes
UPDATE @t 
SET yourXML.modify('delete ENTITY[@NAME="entity1"]/ATTR' )

DECLARE @xml XML = '', @caption VARCHAR(100), @size INT, @width INT

SELECT @caption = 'New Attribute Caption', @size = 200, @width = 300

-- Construct new element with attributes
SET @xml = @xml.query ( '<ATTR ID="attr1" CAPTION="{sql:variable("@caption")}" SIZE="{sql:variable("@size")}" WIDTH="{sql:variable("@width")}"></ATTR> ' )

-- Insert it back in
UPDATE t 
SET yourXML.modify('insert sql:variable("@xml") into (ENTITY[@NAME="entity1"])[1] ')
FROM @t t

SELECT 'after' s, * FROM @t

-- Reconstruct the inner xml END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Create mapping table and loop START
------------------------------------------------------------------------------------------------

DECLARE @i INT = 0
DECLARE @map TABLE ( attributeName VARCHAR(50) PRIMARY KEY, newValue VARCHAR(50) NOT NULL )

INSERT INTO @map ( attributeName, newValue ) VALUES ( 'CAPTION', 'New Attribute Caption 3' )  
INSERT INTO @map ( attributeName, newValue ) VALUES ( 'SIZE', 123 ) 
INSERT INTO @map ( attributeName, newValue ) VALUES ( 'WIDTH', 456 ) 

SELECT 'before 2' s, * FROM @t

WHILE 1=1
BEGIN

    SET @i += 1

    -- Update the XML based on the mapping table
    UPDATE @t 
    SET yourXML.modify('replace value of (/ENTITY/ATTR/@*[local-name()= sql:column("attributeName")])[1] with sql:column("newValue")')
    FROM @t
        CROSS JOIN @map m 
    WHERE yourXML.exist('(/ENTITY/ATTR/@*[local-name()= sql:column("attributeName")][. != sql:column("newValue")])') = 1

    IF @@rowcount = 0 BREAK

    -- Guard against infinite loop
    IF @i > 99 BEGIN RAISERROR( 'Too many loops! %i', 16, 1, @i ) BREAK END

END

SELECT @i loops
SELECT 'after' s, * FROM @t

-- Create mapping table and loop END
------------------------------------------------------------------------------------------------

Thanks, I had gone with reconstructing the entire XML string, making all the changes at once. — Mike D, Aug 20 '12 at 20:16

Update multiple attributes on sql server xml data field at once

2 Answers2

Linked