T-SQL Query to replace a symbol or character in one xml node value of XML data type column?

Question

I want to replace plus symbol with underscore in the one xml node of a particular table. Example: In the below input, i want to replace plus symbol only in filepath node and NOT filename node. Table column data type is XML and NOT varchar.

Input:

 <mediadata>
  <image>
    <FileName>BUF2011-450</FileName>
    <FilePath>/uploadedImages/Products/Indoor_Fun/Puzzles___Brain_Teasers/Puzzles/2000+_Pieces/BUF2011-450.jpg</FilePath>
    <Thumbnails>
      <Thumbnail>
        <FileName>BUF2011-450</FileName>
        <FilePath>/uploadedImages/Products/Indoor_Fun/Puzzles___Brain_Teasers/Puzzles/2000+_Pieces/thumb_BUF2011-450_Large.jpg</FilePath>
      </Thumbnail>
      <Thumbnail>
        <FileName>BUF2011-450</FileName>
        <FilePath>/uploadedImages/Products/Indoor_Fun/Puzzles___Brain_Teasers/Puzzles/2000+_Pieces/thumb_BUF2011-450_Small.jpg</FilePath>
      </Thumbnail>
    </Thumbnails>
  </image>
</mediadata>

@Mitch Wheat I have file path node multiple times in the xml under various decedents. I am not able to figure out for a query to update all of them — DotNetDeveloper
– DotNetDeveloper, Commented Aug 11, 2011 at 16:34
@Mitch Wheat I am all set and have copy pasted the same update statement 3 times in my script to support different xpaths in the xml. — DotNetDeveloper
– DotNetDeveloper, Commented Aug 11, 2011 at 16:59

Mikael Eriksson · Accepted Answer · 2011-08-11 17:30:05Z

4

declare @T table(XMLCol xml)

insert into @T values
('<image>
    <FileName>Tim+bottom</FileName>
    <FilePath>/Top+Bottom/AFX8995+450.jpg</FilePath>
  </image>')

update T set
  XMLCol.modify('replace value of (/image/FilePath[1]/text())[1] 
                 with sql:column("T2.FilePath")')
from @T as T
  cross apply (select replace(XMLCol.value('(/image/FilePath)[1]', 
                                           'varchar(100)'), 
                              '+', 
                              '_')
              ) as T2(FilePath)

Edit

The table variable @T above is instead of your table. Assume that your table is named YourTable and you have one ID column and XMLCol column. The update statement could look like this to change the XML where ID is 1.

update T set
  XMLCol.modify('replace value of (/image/FilePath[1]/text())[1] 
                 with sql:column("T2.FilePath")')
from YourTable as T
  cross apply (select replace(XMLCol.value('(/image/FilePath)[1]', 
                                           'varchar(100)'), 
                              '+', 
                              '_')
              ) as T2(FilePath)      
where T.ID = 1

Edit

It is not possible to update more than one node at a time with .modify(). You have to do this in a loop. The script below uses @T as a test table. You should replace that with whatever your table is called. @T has an ID column and the script assumes that you only update one row at a time and that you know the ID for that row. There are some comments in the code that explains what I do. Don't hesitate to ask if there are something I should make clearer.

-- Test table
declare @T table(ID int, XMLCol xml)

-- Sample data
insert into @T values
(1,
 '<mediadata>
    <image>
      <FileName>BUF2011-450</FileName>
      <FilePath>1/uploadedImages/Products/Indoor_Fun/Puzzles___Brain_Teasers/Puzzles/2000+_Pieces/BUF2011-450.jpg</FilePath>
      <Thumbnails>
        <Thumbnail>
          <FileName>BUF2011-450</FileName>
          <FilePath>2/uploadedImages/Products/Indoor_Fun/Puzzles___Brain_Teasers/Puzzles/2000+_Pieces/thumb_BUF2011-450_Large.jpg</FilePath>
        </Thumbnail>
        <Thumbnail>
          <FileName>BUF2011-450</FileName>
          <FilePath>3/uploadedImages/Products/Indoor_Fun/Puzzles___Brain_Teasers/Puzzles/2000+_Pieces/thumb_BUF2011-450_Small.jpg</FilePath>
        </Thumbnail>
      </Thumbnails>
    </image>
  </mediadata>
')

-- ID for the row you need to update
declare @ID int
set @ID = 1

-- Loop variable, node to update
declare @Pos int
set @Pos = 1

-- The number of nodes to update
declare @Count int

-- Get the number of FilePath nodes
select @Count = XMLCol.query('count(//FilePath)').value('.', 'int')
from @T
where ID = @ID

while @Pos <= @Count
begin
  update T set
    XMLCol.modify('replace value of ((//FilePath)[sql:variable("@Pos")]/text())[1] 
                   with sql:column("T2.FilePath")')
  from @T as T
    cross apply (select replace(T.XMLCol.
                                  query('(//FilePath)[sql:variable("@Pos")]').
                                  value('.', 'varchar(100)'), 
                                '+', 
                                '_')
                ) as T2(FilePath)      
  where T.ID = @ID                 

  set @Pos += 1            
end

edited Aug 11, 2011 at 17:30

answered Aug 11, 2011 at 14:54

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

6 Comments

Code Magician Over a year ago

+1 for doing it right and not 'cheating' by casting to varchar(max)!

DotNetDeveloper Over a year ago

@Mikael Eriksson ..I am kind of confused because I have 1 million records and only few records have problems so how does this work without a where clause to update only those records. can you please write a syntax as if the table and column already exists in the database like update table_name set column_name = '_' etc etc to understand easily

Code Magician Over a year ago

Mikael's code is basically doing: UPDATE TABLE set filepath = REPLACE(filepath, '+', '_') It works differently in XML though. The XML Methods don't permit much tsql, so he is basically joining to a query that exposes that value as a column (the cross apply), applying the TSQL function to perform the replace, then replacing the old value with the new value (xmlCol.modify()).

DotNetDeveloper Over a year ago

@Mikael Eriksson I have file path node multiple times in the xml under various decedents. I am not able to figure out a query to update all of them values..See the updated input in the question

DotNetDeveloper Over a year ago

@Mikael Eriksson .. I am all set and have copy pasted the same update statement 3 times in my script to support different xpaths in the xml. Thank you again for the help

|

Collectives™ on Stack Overflow

T-SQL Query to replace a symbol or character in one xml node value of XML data type column?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related