Updating XML attribute in SQL Server XML column

--Remove the temp table if exisits
IF OBJECT_ID('tempdb..#XMLTemp') IS NOT NULL DROP TABLE #XMLTemp
GO

--Create the temp table
CREATE TABLE #XMLTemp (Id int, Name varchar(32), XML_Data xml)

--Insert values
INSERT INTO #XMLTemp VALUES
(1,        'AAA',        '<MyRoot><Data attr1="1" attr2="11">One</Data></MyRoot>'),
(2,        'BBB',        '<MyRoot><Data attr1="2" attr2="22">Two</Data></MyRoot>'),
(3,        'CCC',        '<MyRoot><Data attr1="3" attr2="33">Threee</Data></MyRoot>'),
(4,        'DDD',        '<MyRoot><Data attr1="4" attr2="44">Four</Data></MyRoot>')

--select the values
SELECT * FROM #XMLTemp

--Select all attribute values in a node where attr2 has value 32
SELECT Name
      ,C.value('@attr1', 'int') as Attribute1
      ,C.value('@attr2', 'int') as Attribute2
      ,C.value('.', 'varchar(10)') as NodeValue
FROM #XMLTemp CROSS APPLY
     #XMLTemp.XML_Data.nodes('MyRoot/Data') as X(C)
where C.value('@attr2', 'int') = 33

--Update attr2 value to 66666666666 if its value is 33
UPDATE #XMLTemp
SET XML_Data.modify('replace value of (/MyRoot/Data[@attr2="33"]/@attr2)[1] with "66666666666" ')

--select the values.Check to see if updated the row or not
SELECT * FROM #XMLTemp