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!

Is it unsave to do `REPLACE ( yourcolumn, 'http://staging.xyz.com/' , 'http://production.xyz.com/' )` on this column? — Edwin Stoteler, Aug 11 '16 at 05:50
I think it may be safe for this exercise, but generally speaking it may be not good solution. That value can be appeared in other places within XML document. — Anton, Aug 11 '16 at 05:55
Then take a look at http://stackoverflow.com/a/1440688/2074825 — Edwin Stoteler, Aug 11 '16 at 06:00
@EdwinStoteler how can i use REPLACE function on xml column? — RaJesh RiJo, Aug 11 '16 at 06:36

score 5 · Accepted Answer · answered Aug 11 '16 at 06:01

5

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

answered Aug 11 '16 at 06:01

Sandip - Frontend Developer

14,397
4
35
61

Alot saver then my comment, but what if the `` tag has attributes. Or sometimes there is a space after the tag? – Edwin Stoteler Aug 11 '16 at 06:05
if attributes there, it predefined so can adjust in update statement – Sandip - Frontend Developer Aug 11 '16 at 06:07
@SandipPatel Thanks, It seems simpler as it look like. let me try on this. – RaJesh RiJo Aug 11 '16 at 06:55

score 5 · Answer 2 · answered Aug 11 '16 at 06:11

With a little help from a couple of XML functions you can do this in a loop. The loop is necessary since replace value of can only replace one value at a time. This code assumes the URL is located first in the node and not embedded in text anywhere.

declare @T table(X xml);

insert into @T(X) values('<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> ');

declare @FromURL nvarchar(100);
declare @ToURL nvarchar(100);

set @FromURL = 'http://staging.xyz.com/';
set @ToURL = 'http://production.xyz.com/';

while 1 = 1
begin
  update @T
  set X.modify('replace value of (//*/text()[contains(., sql:variable("@FromURL"))])[1] 
               with concat(sql:variable("@ToURL"), substring((//*/text()[contains(., sql:variable("@FromURL"))])[1], string-length(sql:variable("@FromURL"))+1))')
  where X.exist('//*/text()[contains(., sql:variable("@FromURL"))]') = 1;

  if @@rowcount = 0
    break;
end;

select *
from @T

replace value of (XML DML)
concat Function (XQuery)
contains Function (XQuery)
string-length Function (XQuery)
sql:variable() Function (XQuery)

@RaJeshRiJo this is for multiple rows- Just replace the table variable `@T` in my code with your real table. — Mikael Eriksson, Aug 11 '16 at 06:38
In my case, I add an error with text(), so I specified the key-value directly: ```UPDATE [svc].[blob] SET props.modify('replace value of (/root/item[key="YOUR_KEY"]/value[contains(., sql:variable("@FromURL"))])[1] with concat(sql:variable("@ToURL"), substring((/root/item[key="YOUR_KEY"]/value[contains(., sql:variable("@FromURL"))])[1], string-length(sql:variable("@FromURL"))+1))') where props.exist('/root/item[key="YOUR_KEY"]/value[contains(., sql:variable("@FromURL"))]') = 1;``` — Flavio Reyes, Aug 16 '23 at 00:38

score 0 · Answer 3 · answered Aug 11 '16 at 05:52

There are many ways to do that.

The first way is to add a WHILE loop. Inside a loop, you search (CHARINDEX) for a position of first tag and first tag. Then, knowing the start and end positions, replace the value. Then on the next iteration you search again, but change starting position in CHARINDEX() function

The second way is to use SELECT ... FROM OPENXML + EXEC sp_xml_preparedocument

Update(Replace partcial value) XML Column in SQL

3 Answers3