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/'')]) ')

James Osborn · Accepted Answer · 2012-03-27 07:30:27Z

11

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)

answered Mar 27, 2012 at 7:30

James Osborn

1,2757 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kberg Over a year ago

Thank you very much James! It's a little bit more "risky" not knowing in which node the replace occurs, but it works fine for me. Thanx!

Collectives™ on Stack Overflow

Update substring in XML column SQL Server 2008

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related