SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Update an XML column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kaunda
Starting Member

Sweden
7 Posts

Posted - 11/06/2013 :  09:26:57  Show Profile  Reply with Quote
Hello, I have a table which contains a number of columns and one of them is an XML column which contains some misspelled information.
I would like to update part of the XML column. How do I do that?
The value of the XML column may look like this:
<ROOT><ADDRESS><STREET>1 Maix street</STREET><CITY>New York</CITY></ADRESS></ROOT>
The table contains quite a few misspelled rows and I want to change all occurrences of the misspelled "Maix street" to "Main street" and only that information. Is this possible to do using a regular SQL Update statement?
Any assistance is greatly appreciated!
/Kaunda

/Kaunda

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/06/2013 :  09:36:33  Show Profile  Reply with Quote
yep. you could simply do this


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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kaunda
Starting Member

Sweden
7 Posts

Posted - 11/07/2013 :  05:19:53  Show Profile  Reply with Quote
Thanks for your reply. I'm not sure that I understand exactly how to do it. I get the following errormessage
"Msg 4421, Level 16, State 1, Line 3
Derived table 't' is not updatable because a column of the derived table is derived or constant."

/Kaunda
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  07:42:46  Show Profile  Reply with Quote
oh ok..I see

try this then

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>



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 11/07/2013 08:00:53
Go to Top of Page

Kaunda
Starting Member

Sweden
7 Posts

Posted - 11/07/2013 :  08:57:59  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/07/2013 :  09:05:00  Show Profile  Reply with Quote
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


Nope..that was just for illustration
you need to use your table and yourcolumn instead of @t
so something like


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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kaunda
Starting Member

Sweden
7 Posts

Posted - 11/08/2013 :  05:06:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/08/2013 :  05:53:40  Show Profile  Reply with Quote
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


how many occurances can happen in a node?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kaunda
Starting Member

Sweden
7 Posts

Posted - 11/11/2013 :  03:37:39  Show Profile  Reply with Quote
Theoretically there can be any number of occurrences within a node, but in most cases there are 2-4 occurrences.

/Kaunda
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000