Update multiple XML elements from SQL Variables

Question

I need to update a value in an XML element with the contents of a TSQL variable. The added complication is that there can be multiple elements with the same name and all elements need to get updated. Here is a sample. Note that customer 1000000 has two customer_firstname elements.

CREATE TABLE Customer_Test
(
    [customer_data] [xml] NULL
) 

-- populate statements
insert into Customer_Test (customer_data) values ('<Customer Note="two customer_firstnames"><customer_id>1000000</customer_id><customer_firstname>Mary</customer_firstname><customer_firstname>Jane</customer_firstname><customer_lastname>Smith</customer_lastname></Customer>');
insert into Customer_Test (customer_data) values ('<Customer Note="normal, no problem"><customer_id>1000001</customer_id><customer_firstname>Joe</customer_firstname><customer_lastname>Bloggs</customer_lastname></Customer>');

The code below works just fine on xml structured like that in the customer_ID = '1000001' record but only the first customer_firstname element gets updated for situations like customer_ID = '1000000'

DECLARE @newName varchar(10) = 'xxx'
DECLARE @IDValue varchar(10) = '1000000'
UPDATE  [Customer_Test] 
SET  customer_data.modify('replace value of (Customer/customer_firstname/text())[1] with sql:variable("@newName")') 
WHERE  customer_data.value('(/Customer/customer_id)[1]','varchar(50)') = @IDValue

I am really stuck on this - I need all values of the customer_firstname element to be set to the same value if they are present. I half suspect a CROSS APPLY is required but all my attempts to code one would not compile.

I would very much value any advice which might be provided. Thanks in advance.

Mikael Eriksson · Accepted Answer · 2012-08-31 11:54:18Z

It is not possible to update more than one value in the XML with one update statement.

You can do it in a while loop that iterates the number of first names you have in one XML.

DECLARE @newName varchar(10) = 'xxx'
DECLARE @IDValue varchar(10) = '1000000'
DECLARE @FirstNameCount INT

-- Get the max number of first names in one XML
SELECT @FirstNameCount = max(customer_data.value('count(Customer/customer_firstname)', 'int'))
FROM Customer_Test
WHERE customer_data.value('(/Customer/customer_id)[1]','varchar(50)') = @IDValue

-- Loop over @FirstNameCoount
WHILE @FirstNameCount > 0
BEGIN
  UPDATE  [Customer_Test] 
  SET  customer_data.modify('replace value of (Customer/customer_firstname[sql:variable("@FirstNameCount")]/text())[1] with sql:variable("@newName")')
  WHERE  customer_data.value('(/Customer/customer_id)[1]','varchar(50)') = @IDValue

  SET @FirstNameCount = @FirstNameCount - 1
END

wBob · Accepted Answer · 2012-09-03 10:20:57Z

Isn't it worth deduplicating your XML at this point? If you are setting all values to the same thing then there is really no point in storing the second name. You can delete the second customer_firstname elements based on their position, eg

SELECT 'before' s, DATALENGTH( customer_data ) dl, [customer_data] FROM Customer_Test

UPDATE [Customer_Test]
SET  customer_data.modify('delete Customer/customer_firstname[position() > 1]')  

SELECT 'after 1' s, DATALENGTH( customer_data ) dl, [customer_data] FROM Customer_Test

DECLARE @newName varchar(10) = 'xxx' 
DECLARE @IDValue varchar(10) = '1000000' 
UPDATE [Customer_Test]
SET  customer_data.modify('replace value of (Customer/customer_firstname/text())[1] with sql:variable("@newName")')  
WHERE  customer_data.value('(/Customer/customer_id)[1]','varchar(50)') = @IDValue 

SELECT 'after 2' s, DATALENGTH( customer_data ) dl, [customer_data] FROM Customer_Test

Collectives™ on Stack Overflow

Update multiple XML elements from SQL Variables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related