Update XML as Upper Case in SQL Server

Question

I store an XML string in a column in my SQL Server database. I'm trying to run an UPDATE statement that will make a certain field into Upper Case. I can do that easily during a SELECT but can't get it to work with an update.

This SELECT statement works
select Upper(XmlTest.value('(/CodeFiveReport/Owner/@UnitNumber)[1]', 'varchar(10)')) as UnitNumber from uploadreport

But I want to update the XML as that permanently
Update table Set XmlString.Modify('replace value of (/Root/Node/@Field)[1] with ?

Answer 1

Use the XQuery function upper-case

Answer 2

upper-case was added to SQL Server in SQL Server 2008, so there's no good solution prior to that unless you want to use cursors. The following works in SQL Server 2008:

declare @xml xml

set @xml = N'<CodeFiveReport><Owner UnitNumber="Mixed"/></CodeFiveReport>'
select T1.C1.value('upper-case((/CodeFiveReport/Owner/@UnitNumber)[1])', 'varchar(10)') from @xml.nodes('/') T1(C1)

SET @xml.modify('
                replace value of (/CodeFiveReport/Owner/@UnitNumber)[1]
                with xs:string(upper-case((/CodeFiveReport/Owner/@UnitNumber)[1]))
                ')

select @xml