replace - 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

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-04-24 : 03:12:58

Hi,
There appears to be the word "&" present in xml or nvarchar datatype fields of various tables.
This should really be "&" and not "&"

I am thinking of running an update query to replace as follows for the varchar fields:

update tablename
set fieldname = replace(fieldname, "&", "&")

p.s. I do not know how to get the undesired word for you to see because it seems the UI here ignore the ampersandAmp;

Questions:
1) is the above query correct to run for this purpose?
2) what do I do for the fields which are of type xml?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 03:27:40

You have to check for entitization, which is not that hard.
Do you have some examples or sample data?
And expected output?

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-04-24 : 04:20:05

Hello,
Is this the correct way to update an xml field?

BUT, the error is:
XQuery [@Sample.xml.modify()]: Invalid entity reference
Thanks

declare @Sample table
(
UNID varchar(50),
[xml] XML
)

INSERT @Sample
SELECT 'x12345','<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL
SELECT 'x4321', NULL UNION ALL
SELECT 'x9876', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value amp; 2</value></stringList>' UNION ALL
SELECT 'x6789', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value & 4</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

select * from @Sample

UPDATE s
SET [xml].modify('
declare default element namespace "http://schemas.myCompany.com/myProject/[app]/stringList/1.0";
replace value of ((/stringList/value[.="&"]/text())[1])
with "&"
')
FROM @Sample as s

SELECT * FROM @Sample

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources