How to replace a part of XML node in SQL Server 2005

Question

I would like to know how I can update a part of the text of an XML node in SQL Server 2005 using xquery

In the following example I would like to replace the word "very" with "excellent"

    declare @xml as xml
    set @xml = '<root><info>well hello this is a very good example</info></root>'
    declare @replacement as varchar(50)
    set @replacement = 'excellent'
    declare @search as varchar(50)
    set @search = 'very'

    set @xml.modify('replace value of (/root/info/text())[1]
                     with replace((/root/info/text())[1],sql:variable("@search"),sql:variable("@replacement"))'
        )
    select @xml

Any help would be appreciated

score 2 · Accepted Answer · answered May 03 '11 at 12:31

Since this word you want to replace isn't the contents of a XML tag - but only part of that content - you might just want to check out text-based replacement options.

It would work if you had something like this:

declare @xml as xml

set @xml = '<root><info>well hello this is a <rating>very good</rating> example</info></root>'

Then, you could reach into the XML and replace the contents of <rating>....</rating> with something else:

declare @replacement as varchar(50)
set @replacement = 'excellent'

set 
    @xml.modify('replace value of (/root/info/rating/text())[1]
                 with sql:variable("@replacement")')

select @xml

But as it stands now, you probably have to grab the textual contents of the <info> and do a textual replace on that:

DECLARE @xml as XML
SET @xml = '<root><info>well hello this is a very good example</info></root>'

DECLARE @newcontent VARCHAR(1000)
SELECT @newcontent = @xml.value('(/root/info/text())[1]', 'VARCHAR(1000)')

-- replace "very" with "excellent"    
SELECT @newcontent = REPLACE(@newcontent, 'very', 'excellent')

SET 
@xml.modify('replace value of (/root/info/text())[1]
                 with sql:variable("@newcontent")')

SELECT @xml

But what if the content of the info tag is greater then varchar(1000), more like a nvarchar(max), how do you deal with that situation? (ie: the content of info is a html block and you would replace a value in the html block via the database) — Peter, May 04 '11 at 08:54
@peter: as long as the content is not really the full content of an XML tag (or XML attribute), I think this approach here is bad anyway. I would do this in an application using that data - not within SQL Server itself. Only if you can replace a complete XML tag's content with a new content is this approach advisable, in my opinion. — marc_s, May 04 '11 at 11:26

How to replace a part of XML node in SQL Server 2005

1 Answers1