Update(Replace partcial value) XML Column in SQL

Question

I have an XML column in my Table and i wanted to replace particular text wherever it appear in that column with a new text. Here is the xml structure,

<Story>
 <StoryNonText>
  <NonText>
   <ImageID>1</ImageID>
   <Src>http://staging.xyz.com/FolderName/1.png</Src>
  </NonText>
  <NonText>
   <ImageID>2</ImageID>
   <Src>http://staging.xyz.com/FolderName/2.png</Src>
  </NonText>
 </StoryNonText>
</Story> 

In the above XML I wanted to replace all the <Src> values having http://staging.xyz.com/ to http://production.xyz.com/. Please guide me how i can do this!

Answer 1

You can use Replace() function as below:

Update TableName
SET
ColumnName=replace(CAST(ColumnName AS VARCHAR(8000)),'<Src>http://staging.xyz.com/','<Src>http://production.xyz.com/')