SQL Server 2008 search and replace xml node values

Have a look at the following MSDN article:

replace value of (XML DML)

Specifically, you might try something like this:

-- Setup test data
declare @table table (
    col nvarchar(max) not null
)
insert into @table select
'<memberHours type="TeamHours[]"> 
<item> 
  <member type="String">Bill</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
<item> 
  <member type="String">John</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
<item> 
  <member type="String">Sally</member> 
  <hours type="Decimal">0.0</hours> 
</item> 
</memberHours>'

-- Set search/replace vars
declare @oldval nvarchar(max) = 'John'
declare @newval nvarchar(max) = 'Jon'
declare @oldcol xml
declare @newcol xml

-- Loop over records fitting the search
while exists (
    select null
    from (
        select cast(col as xml) as col
        from @table
    ) as a
    where col.exist('/memberHours/item/member[(text()[1]) eq sql:variable("@oldval")]') = 1
) begin

    -- Grab a record as xml
    set @oldcol = (
        select top 1 col
        from (
            select cast(col as xml) as col
            from @table
        ) as a
        where col.exist('/memberHours/item/member[(text()[1]) eq sql:variable("@oldval")]') = 1
    )
    set @newcol = @oldcol

    -- Modify xml data
    while @newcol.exist('/memberHours/item/member[(text()[1]) eq sql:variable("@oldval")]') = 1 begin
        set @newcol.modify('
            replace value of (/memberHours/item[member=sql:variable("@oldval")]/member/text())[1] with sql:variable("@newval")
        ')
    end

    -- Update table
    update @table
    set col = cast(@newcol as nvarchar(max))
    where cast(cast(col as xml) as nvarchar(max)) = cast(@oldcol as nvarchar(max)) -- Cast both for equality test!

end

-- Test output
select * from @table