CODEWITHSUNDEEP
xmlsql-server-2008listreplace
Keith Miller
Keith Miller

Reputation: 155

SQL Server 2008 search and replace xml node values

I have a column in a table that contains xml that looks like the following 

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

I need to be able to find all of the rows where the member is equal to 'John' and then replace 'John' with 'Jon'. Since my xml is stored in an nvarchar(max) column, I am writing a function that casts the column to an xml variable that I can then use. What I cannot figure out is how to find which 'Item' matches and how to replace just that one value (i.e. just 'John')

I am on SQL server 2008.

Upvotes: 2

Views: 2894

Answers (1)

Tim Lehner
Tim Lehner

Reputation: 15261

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

Upvotes: 3

Related Questions