Replace text in XML - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replace text in XML

Author

Topic

VD
Starting Member

5 Posts

Posted - 2010-06-07 : 13:02:24

I have a need to replace all email ids within an XML in a text column. The emails are different in each row and I want to do something like

update Tablename set contents=replace(cast(contents as varchar(MAX)),'<XMLNodeName>%</XMLNodeName>','<XMLNodeName>myemail@company.com</XMLNodeName>')

* The "%" kind of represents all the email ids

Is there anyway to do an update like this?

VD

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-07 : 13:29:32

Try this

create table #t (emailid text)
insert #t 
select '<XMLNodeName>test@test.com</XMLNodeName>'
union all select '<XMLNodeName>test@test1.com</XMLNodeName>'
union all select '<Root><XMLNodeName>test@test1.com</XMLNodeName></Root>'

update #t
set emailid = cast(substring(convert(varchar(max),emailid), 1,charindex('<XMLNodeName>',convert(varchar(max),emailid)) + 12) + 'myemail@company.com' + 
substring(convert(varchar(max),emailid),charindex('</XMLNodeName>',convert(varchar(max),emailid)), len(convert(varchar(max),emailid))- charindex('</XMLNodeName>',convert(varchar(max),emailid))+ 1) as text)

select * from #t

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-07 : 14:43:09

See http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx
and "replace value of".

N 56°04'39.26"
E 12°55'05.63"

VD
Starting Member

5 Posts

Posted - 2010-06-07 : 17:34:16

quote:

Originally posted by vijayisonly

Try this

create table #t (emailid text)
insert #t 
select '<XMLNodeName>test@test.com</XMLNodeName>'
union all select '<XMLNodeName>test@test1.com</XMLNodeName>'
union all select '<Root><XMLNodeName>test@test1.com</XMLNodeName></Root>'

update #t
set emailid = cast(substring(convert(varchar(max),emailid), 1,charindex('<XMLNodeName>',convert(varchar(max),emailid)) + 12) + 'myemail@company.com' + 
substring(convert(varchar(max),emailid),charindex('</XMLNodeName>',convert(varchar(max),emailid)), len(convert(varchar(max),emailid))- charindex('</XMLNodeName>',convert(varchar(max),emailid))+ 1) as text)

select * from #t

This worked. Thanks a lot.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 03:30:08

Yes, and is so much simpler than doing xml stuff directly! See this simple repro

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		eMailID XML
	)

INSERT	@Sample
	(
		eMailID
	)
SELECT	'<XMLNodeName>test@test.com</XMLNodeName>' UNION ALL
SELECT	'<XMLNodeName>test@test1.com</XMLNodeName>' UNION ALL
SELECT	'<Root><XMLNodeName>test@test1.com</XMLNodeName></Root>' UNION ALL
SELECT	'<Root><yak><XMLNodeName>test@test1.com</XMLNodeName></yak></Root>'

DECLARE	@Status VARCHAR(200)
SET	@Status = 'peso@developerworkshop.net'

SELECT	*
FROM	@Sample

-- vijayisonly
UPDATE	@Sample
SET	eMailID = CAST(
			SUBSTRING(CONVERT(NVARCHAR(MAX), eMailID), 1, CHARINDEX('<XMLNodeName>', CONVERT(NVARCHAR(MAX), eMailID)) + 12)
			+ @Status
			+ SUBSTRING(CONVERT(NVARCHAR(MAX), eMailID), CHARINDEX('</XMLNodeName>', CONVERT(NVARCHAR(MAX), eMailID)), LEN(CONVERT(NVARCHAR(MAX), eMailID)) - CHARINDEX('</XMLNodeName>', CONVERT(NVARCHAR(MAX), eMailID)) + 1)
		AS XML)

SELECT	*
FROM	@Sample

-- Peso
UPDATE	@Sample
SET	eMailID.modify('replace value of (//XMLNodeName/text())[1] with sql:variable("@Status")') 

SELECT	*
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 03:41:43

Also try with this sample data.
Vijays code will bomb because there is no correct node name in one of the records.
Also, Vijays code is not case sensitive so that a incorrect node will be updated.

SELECT	'<XMLNodeName>test@test.com</XMLNodeName>' UNION ALL
SELECT	'<XMLNodeName>test@test1.com</XMLNodeName>' UNION ALL
SELECT	'<Root><XMLNodeName>test@test1.com</XMLNodeName></Root>' UNION ALL
SELECT	'<XMLNodename>yak@sqlteam.com</XMLNodename>' UNION ALL   -- Vijays code will incorrectly update this node too!
SELECT	'<a>yak@sqlteam.com</a>' UNION ALL -- Vijays code will bomb out!
SELECT	'<Root><yak><XMLNodeName>test@test1.com</XMLNodeName></yak></Root>'

N 56°04'39.26"
E 12°55'05.63"

VD
Starting Member

5 Posts

Posted - 2010-06-08 : 07:44:42

quote:
Originally posted by Peso

Also try with this sample data.
Vijays code will bomb because there is no correct node name in one of the records.
Also, Vijays code is not case sensitive so that a incorrect node will be updated.
SELECT '<XMLNodeName>test@test.com</XMLNodeName>' UNION ALL
SELECT '<XMLNodeName>test@test1.com</XMLNodeName>' UNION ALL
SELECT '<Root><XMLNodeName>test@test1.com</XMLNodeName></Root>' UNION ALL
SELECT '<XMLNodename>yak@sqlteam.com</XMLNodename>' UNION ALL -- Vijays code will incorrectly update this node too!
SELECT '<a>yak@sqlteam.com</a>' UNION ALL -- Vijays code will bomb out!
SELECT '<Root><yak><XMLNodeName>test@test1.com</XMLNodeName></yak></Root>'

N 56°04'39.26"
E 12°55'05.63"

Hi Peso,

I do want the following node to get updated too
<XMLNodename>yak@sqlteam.com</XMLNodename> i.e. any email within that specific node should get replaced with my email id.

Your method does seem simpler and i'd certainly like to try it out but one problem I have is that the data stored in the table is currently in text format and not XML.

So your procedure needs to start with
DECLARE @Sample TABLE
(
eMailID Text
)

Can you provide a response on how your method can still achieve the results needed.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 08:06:56

1) Why are you using TEXT for storing XML data? At least, you should be using NTEXT, since XML handles unicode.
2) XML is case sensitive by default. The two node names are not the same.

N 56°04'39.26"
E 12°55'05.63"

VD
Starting Member

5 Posts

Posted - 2010-06-08 : 08:18:36

#1 Unfortunately the company that developed it, did it that way and we need to live with that, at least for a while.
#2 Got it.

VD
Starting Member

5 Posts

Posted - 2010-06-08 : 11:34:37

quote:

Originally posted by vijayisonly

Try this

create table #t (emailid text)
insert #t 
select '<XMLNodeName>test@test.com</XMLNodeName>'
union all select '<XMLNodeName>test@test1.com</XMLNodeName>'
union all select '<Root><XMLNodeName>test@test1.com</XMLNodeName></Root>'

update #t
set emailid = cast(substring(convert(varchar(max),emailid), 1,charindex('<XMLNodeName>',convert(varchar(max),emailid)) + 12) + 'myemail@company.com' + 
substring(convert(varchar(max),emailid),charindex('</XMLNodeName>',convert(varchar(max),emailid)), len(convert(varchar(max),emailid))- charindex('</XMLNodeName>',convert(varchar(max),emailid))+ 1) as text)

select * from #t

I am having a problem with this method when a value for <XMLNodeName> does not exist i.e Only <XMLNodeName/> exists instead of <XMLNodeName> something@domain.com </XMLNodeName> .
Any suggestions on how to counter this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-08 : 14:33:03

I wrote this would happen 06/08/2010 : 03:41:43
For the UPDATE clause, add two WHERE like this

UPDATE #t
SET ...
WHERE convert(varchar(max), emailid) LIKE '<XMLNodeName>%</XMLNodeName>'

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources