Updating an attribute in multiple xml nodes with transact SQL XML

I ran into a situation where I needed to update an attribute of each node of XML that is stored in a column of one of our database tables. This logic needed to be performed in a stored procedure, so I needed to make use of the XML functionality of SQL Server 2008.

First Thought:
1) set an xml variable value to the value from the table
2) loop through the xml nodes and update the attribute values
3) execute an update statement to place the xml variable value back into the record's column value.


Second Thought:
1) set an xml variable value to the value from the table
2) Select xml into a temp table and use SQL Update statements (much cleaner than looping)
3) execute an update statement to place the xml variable value back into the

So after much more thinking my second thought seemed like a cleaner coding choice. Here is an example of how to do this.

The XML value in column [Style] of table [CustomProject] is as follows:
<Styles>
<Style id="60161" sortorder="1" />
<Style id="60152" sortorder="2" />
<Style id="60159" sortorder="3" />
<Style id="60158" sortorder="4" />
<Style id="60191" sortorder="5" />
<Style id="60162" sortorder="6" />
<Style id="60160" sortorder="7" />
</Styles>

I place the XML into a variable as follows:

--declare before and after variables
declare @OldStyleXML as XML
declare @NewStyleXML as XML

--place the CustomProjects.StyleXML into the xml variable
SELECT @OldStyleXML = p.StyleXML
FROM CustomProjects p
WHERE p.CustomProjectID = 12345

--create the temp table to dump the xml data into
create table #StylesXML
(
styleid int null,
sortorder int null
)

--insert into the temp table from the xml variable
insert into #StylesXML
( styleid,
[sortorder]
)
select
Tab.Col.query('.').value('(//@id)[1]','int') AS 'styleid',
Tab.Col.query('.').value('(//@sortorder)[1]','int') AS 'sortorder'
FROM
@OldStyleXML .nodes('//Style) as Tab(Col)


--perform normal SQL update statement to update the information to what you want. My example updates the old styleIDs to the new styleIDs based on styletypeid and with the assumption that the old styles sortorder and new styles sortorder are their equivalent replacements.
update #StylesXML
set styleid = NewStyle.styleid
from ( select s.*
from #StylesXML temp,
Styles s
where temp.styleid = s.styleid) OldStyles,
( select s.*
from styles s
where s.StyleTypeID = @NewStyleTypeID) NewStyles where OldStyles.SortOrder = NewStyles.SortOrder
and #StylesXML.styleid = OldStyles.styleid


--Put the new updated styles back into the xml variable with <style> tags wrapping each element
SET @NewStyleXML = (
SELECT styleid AS [style/@id]
,sortorder AS [style/@sortorder]
FROM #StylesXML
FOR XML PATH('') )

--Wrap the entire set of tags with <styles> "plural" tag
SET @NewStyleXML = (
SELECT @NewStyleXML
FOR XML PATH('styles')
)

--Put it back into the record
UPDATE CustomProjects p
SET p.StyleXML = @NewStyleXML
WHERE p.CustomProjectID = 12345

That is it!