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
– marc_s, Commented Aug 9, 2012 at 19:37
Possible duplicate of TSQL 2005, XML DML - Update Two Values at once? — Yishai
– Yishai, Commented Aug 11, 2016 at 2:53

Mikael Eriksson · Accepted Answer · 2012-08-09 19:47:11Z

4

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 9, 2012 at 19:47

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wBob · Accepted Answer · 2012-08-13 15:13:43Z

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.

Collectives™ on Stack Overflow

Update multiple attributes on sql server xml data field at once

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related