xml 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)

xml replace

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-05-13 : 02:21:26

Hello,
I raised a query a little while ago regarding updating and replacing the values in an xml field but no one seems to know the answer.
So here is the question in case someone is familiar with the solution.
Basically the update query should replace the occurances of "&" to "&"
I have only managed to do this one at a time and do not see how to run this query for all the records in the table.
I have produced a sample for you below.

Hope someone can help.

Thank you

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

INSERT @Sample

SELECT 'x12345','<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1234</value></stringList>','<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value1</value></stringList>' UNION ALL

SELECT 'x4321', NULL,NULL UNION ALL

SELECT 'x9876', '<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>valuex</value></stringList>','<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>value5</value><value>value2</value></stringList>' UNION ALL

SELECT 'x6789', NULL,'<stringList xmlns="http://schemas.myCompany.com/myProject/[app]/stringList/1.0"><value>Alliance & Leicester Group Treasury plc</value><value>value1</value><value>value5</value><value>value2</value></stringList>'

select * from @Sample

declare @NewDescription varchar(1000)
set @NewDescription = 'Alliance & Leicester Group Treasury plc'

UPDATE A

SET [xml].modify('

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

replace value of ((/stringList/value[.="Alliance & Leicester Group Treasury plc"]/text())[1])

with sql:variable("@NewDescription")

')

FROM @Sample AS A
where A.ID = 'x6789'

SELECT * FROM @Sample

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-05-13 : 07:28:25

Hello again all,
Apparently this is the limitation of xml in sql server (So I have been told) as modify works only for one value.
Anyway, to do this you need to simply convert to varchar(max) whilst doing the update.
Thanks anyway...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources