replace function in xml field - 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 function in xml field

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-05-01 : 05:38:51

Hello,
I have managed to come up with a query to update an xml field.
Do you know how I can modify this query so that all the occurences of '&' are replaced with '&'
Thanks

declare @Sample table
(
RowID varchar(50),
[xmlField] XML
)

INSERT @Sample
SELECT 'x12345','<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value & 1</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 & 2</value></stringList>' UNION ALL
SELECT 'x6789', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value4</value><value>value1</value><value>value & 5</value><value>value2</value></stringList>'

select * from @Sample
----
declare @ReplaceWith varchar(10)
set @ReplaceWith = '&'

UPDATE p

SET [xmlField].modify('

declare default element namespace "http://schemas.mycompany.com/myproject/[app]/stringList/1.0";

replace value of ((/stringList/value[.="'&'"])[1])

with sql:variable("@ReplaceWith")

')

FROM @Sample AS p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources