How do I modify the node contents in an SQL XML column?

Question

I have some data stored in an SQL Server XML data column in a table. It's used to configure HTML content (to be sent as emails) and works fine. However, I would now like to change some of the data, but I can't seem to find the correct incantation to effect just the bits I want.

Take the following as an example.

declare @s1 varchar(max);
declare @s2 varchar(max);
declare @s3 varchar(max);

-- rough approximation of my data
set @s1 = '<email sender="bob"><html><body><table><tbody><tr><td><b>'
set @s2 = '</b></td></tr><tr><td><b>'
set @s3 = '</b></td></tr></tbody></table></body></html></email>'

declare @t table (id int, data xml)

insert into @t values (1, @s1 + 'Hello World' + @s2 + 'Goodbye cruel world' + @s3)
insert into @t values (2, @s1 + 'Hello World' + @s2 + 'I''m leaving you today' + @s3)
insert into @t values (3, @s1 + 'Hello World' + @s2 + 'Goodbye, goodbye, goodbye' + @s3)

select data from @t

update @t -- change sender to "fred"
set data.modify('replace value of (/email/@sender)[1] with "fred"')

select data from @t

select data,x.nd.value('b[1]','varchar(max)') -- find the "hello world" bits
from @t
cross apply data.nodes('email/html/body/table/tbody/tr/td') as x(nd)
where x.nd.value('b[1]','varchar(max)') = 'Hello World'

I want to change the "sender" data (which I think I've worked out), and the "Hello World" data - which I can find, but I don't know how to change. I'm not really sure what's going on with the "cross apply" syntax either, and guess that I could write the XPath stuff to only find the "Hello World" nodes without the "where" clause ?

My table only has about 9 rows in it, so I could write nine update commands if needed and this is a one-off data maintenance task.

Any help would be appreciated.

Edit:

Is this the way its done ?

update @t
set data.modify('replace value of (email/html/body/table/tbody/tr/td/b/text())[1] with "bonjour monde"')
from @t
cross apply data.nodes('email/html/body/table/tbody/tr/td') as x(nd)
where x.nd.value('b[1]','varchar(max)') = 'Hello World'

Thanks.

Remus Rusanu · Accepted Answer · 2012-11-26 12:22:57Z

Almost :) Your example from the Edit updates the first email/html/body/table/tbody/tr/td/b but the WHERE clause looks for any email/html/body/table/tbody/tr/td. So if you have an email that has, say, two tr and the second one matches the filter you still update the first one. Eg.

 insert into @t values (4, '<email sender="bob"><html><body><table><tbody>
   <tr><td><b>Ciao mondo!</b></td></tr><tr><td><b>Goodbye, goodbye, goodbye</b></td></tr>
   <tr><td><b>Hello World</b></td></tr><tr><td><b>Can''t get rid of me!</b></td></tr>
   </tbody></table></body></html></email>');

The xml above will not be updated correctly: it will change the first tr which does not actually matches the filter, and it will leave the second one unaffected although is the one that should had been changed. There are several ways to approach this. Personally I would make the XPath/XQuery in the .modify do the work for me. I would also critique the use of cross apply when the XML.exist would suffice:

update @t
set data.modify('replace value of (email/html/body/table/tbody/tr/td/b[.="Hello World"]/text())[1] with "bonjour monde"')
from @t
where data.exist('email/html/body/table/tbody/tr/td/b[.="Hello World"]') = 1;

This will be more correct, but it must be run repeatedly as only one XML element at a time can be modified (ie. if two tr have Hello World then the modify will only update the first one).

Probably all these points are academic for you, since your first update likely did the job. But for future reference. BTW, learn how XPath filters work, they are tremendously helpful.

Collectives™ on Stack Overflow

How do I modify the node contents in an SQL XML column?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related