Update substring in XML column SQL Server 2008

Question

I want to perform an update on all the rows in an XML column in SQL Server 2008, replacing just a substring in a certain xml node. I'm new to XML DML / XQuery and all that and I just cannot figure out how to do it.

Example of XML in the column I want to update:

<d>
  <p n="Richedit01" t="System.String">
    <v>
      &lt;p&gt; &lt;img border="0" alt="Football4" src="/$-1/football4.jpg" /&gt; &lt;/p&gt;
    </v>
  </p>
</d>  

I want to replace all occurances of the substring 'src="/$-1/' with 'src="/$-1/file/' in all rows.

Tried this but apparently there is no way that I can use the XPath replace function like this:

UPDATE Translation
SET ContentData.modify('replace value of (d/p[@t=''System.String'']/v)[1] with (d/p[@t=''System.String'']/v[replace(.,''src="/$-1/'',''src="/$-1/file/'')]) ') 

Answer 1

I think the easiest solution is to convert to another datatype that can accept replace (nvarchar(MAX) for example). Use the REPLACE function, and then convert it back to XML

Something like

UPDATE Translation SET
ContentData = CAST(REPLACE(CAST(ContentData AS NVARCHAR(MAX)), '/$-1/','/$-1/file/') AS XML)