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
– Edwin Stoteler, Commented Aug 11, 2016 at 5: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
– Anton, Commented Aug 11, 2016 at 5:55
@EdwinStoteler how can i use REPLACE function on xml column? — RaJesh RiJo
– RaJesh RiJo, Commented Aug 11, 2016 at 6:36

Sandip - Frontend Developer · Accepted Answer · 2016-08-11 06:01:38Z

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, 2016 at 6:01

Sandip - Frontend Developer

14.7k5 gold badges38 silver badges62 bronze badges

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

3 Comments

Edwin Stoteler Over a year ago

Alot saver then my comment, but what if the <src> tag has attributes. Or sometimes there is a space after the tag?

Sandip - Frontend Developer Over a year ago

if attributes there, it predefined so can adjust in update statement

RaJesh RiJo Over a year ago

@SandipPatel Thanks, It seems simpler as it look like. let me try on this.

Mikael Eriksson · Accepted Answer · 2016-08-11 06:11:23Z

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.
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;

Anton · Accepted Answer · 2016-08-11 05:52:17Z

0

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

answered Aug 11, 2016 at 5:52

Anton

2,8821 gold badge12 silver badges15 bronze badges

Collectives™ on Stack Overflow

Update(Replace partcial value) XML Column in SQL

3 Answers 3

3 Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Linked

Related