Update substring in SQL Server 2008 XML column

Question

I want to perform an update on all rows of an XML column in SQL Server 2008, replacing only the substring in the specific xml node. I am new to XML DML / XQuery and all that, and I just can't figure out how to do this.

Sample XML in the column I want to update:

<d> <pn="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 entries of the substring 'src = "/ $ - 1 /' with 'src =" / $ - 1 / file /' in all lines.

Tried this, but apparently I can't use the XPath replacement function as follows:

 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 data type that can accept a replacement (e.g. nvarchar (MAX)). 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)