Hi all,
I've got a varchar column containing xml data that I need to update. Instead of writing a complicated string update (ie lots of substring, charindex etc) I thought I'd use OPENXML to put the data into a table that I could update. All worked fine until...you guessed it.. I tried to use FOR XML to generate the XML string from my updated resultset to update the original column. As I learnt, you can't use FOR XML to do this.
So does anybody have a way of updating an XML string in a varchar column without going down the substring/replace etc path?
Specifically I need to add another element to the string.....
e.g
<customer><FirstName>Robert</FirstName><Surname>Smith</Surname></customer>
needs to become
<customer><FirstName>Robert</FirstName><Surname>Smith</Surname><Manager>Bobby</Manager></customer>
Cheers