UPDATE t
SET Col = REPLACE(Col,'Maix Street','Main Street')
FROM (
SELECT CAST(XMLCol AS varchar(max)) AS Col FROM Table
WHERE CAST(XMLCol AS varchar(max)) LIKE '%Maix Street%'
)t

declare @t table
(
x xml
)

insert @t
values('<ROOT><ADDRESS><STREET>1 Maix street</STREET><CITY>New York</CITY></ADDRESS></ROOT>'),
('<ROOT><ADDRESS><STREET>2 Main street</STREET><CITY>New York</CITY></ADDRESS></ROOT>'),
('<ROOT><ADDRESS><STREET>3 Maix street</STREET><CITY>New York</CITY></ADDRESS></ROOT>')


SELECT x,x.query('data(/ROOT/ADDRESS/STREET)') AS Strt FROM  @t
DECLARE @str varchar(50 )= '1 Main Street'

UPDATE t
SET x.modify('replace value of (/ROOT[1]/ADDRESS[1]/STREET/text())[1] with sql:column("Strt")')
FROM (
SELECT x,REPLACE(x.query('/ROOT/ADDRESS/STREET').value('.','varchar(100)'),'Maix','Main') AS Strt 
FROM  @t) t


SELECT * FROM @T

output
-----------------------------
x
-----------------------------
<ROOT><ADDRESS><STREET>1 Main street</STREET><CITY>New York</CITY></ADDRESS></ROOT>
<ROOT><ADDRESS><STREET>2 Main street</STREET><CITY>New York</CITY></ADDRESS></ROOT>
<ROOT><ADDRESS><STREET>3 Main street</STREET><CITY>New York</CITY></ADDRESS></ROOT>

quote:

---

Originally posted by Kaunda

Thanks again! Isn't your solution just a way to create and update a temporary table? I have to update a permanent table named LOGG which contains +100.000 rows. The LOGG table has a numer of columns and one of them is the XML column lets call it ADDRESS and it's just some of the node values in that particular column that I want to modify (the misspelled ones). The misspelled node values can be found anywhere in the XML column and there might be more than one misspelled values in the same row and column!

/Kaunda

---

UPDATE t
SET ADDRESS.modify('replace value of (/ROOT[1]/ADDRESS[1]/STREET/text())[1] with sql:column("Strt")')
FROM (
SELECT ADDRESS,REPLACE(ADDRESS.query('/ROOT/ADDRESS/STREET').value('.','varchar(100)'),'Maix','Main') AS Strt 
FROM  LOGG) t

quote:

---

Originally posted by Kaunda

Thanks a lot, it works pretty good right now! But it only updates the first occurrence of each row and in my case there can be many occurrences on a row. Is it possible to solve that? or do I have to run it several times and change the [1] to [2] and so on?


/Kaunda

---