Updating XML stored in a column via update text function????

• AirBasi

  SSC-Addicted

  Points: 463

  More actions

  August 27, 2010 at 9:50 am

  #228378

  Hey Gurus and Grand Masters

  I have xml stored in a column with data type = text. I need to update multiple substrings of data within that text column (ie update ‘<data>1</data>’ to ‘<data>Test</data>’, ‘<data>2</data>’ to ‘<data>Test2</data>’). In trying the Replace function, it returns this message: Argument data type text is invalid for argument 1 of replace function.

  In light of this, I am using the updatetext function instead. Due to the column data type of text, this function requires a delete_lengthvalue in bytes. Right now I have string length, I am looking for the best way to calculate byte length. Is there a built-in function to do this? Multiplying by a factor seems to work in some cases but not all, I am still in the midst of testing this…

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 27, 2010 at 10:14 am

  #1213083

  Change the text data type to XML. This is not only to match data type and data content. You'll also get rid of a deprecated data type.

  Once this is done you can use XQuery to modify the xml data in your table. It'll be a lot easier (once you got used to it).

  A good resource for XQuery is Jacob Sebastians blog.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]