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