Updating XML data stored as varchar

• pmckay

  Mr or Mrs. 500

  Points: 514

  More actions

  November 10, 2003 at 4:26 am

  #84731

  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

• linto

  SSC Eights!

  Points: 851

  More actions

  November 10, 2003 at 5:06 pm

  #481503

  I don't see any other method than parsing the string and updating the table as you mentioned in the question...

  XML integration is only in the initial stages and by YUKON, you may be able to write anything you want for XML based development.

  Linto