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

update xml field

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-04-25 : 16:32:06

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-25 : 16:47:42

Also asked here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/0d900556-8ca4-42c6-a765-5b305badaaf9

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-04-26 : 04:22:27

quote:
Originally posted by Peso

Also asked here
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/0d900556-8ca4-42c6-a765-5b305badaaf9

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

Hello,
Is it not ok, I ask the question in another forum so that someone might know the asnwer?
Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-26 : 04:24:08

Of course it is ok to ask both here and there!
I personally just don't want to spend time on this question if already answered on another forum.

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2009-04-26 : 16:38:49

Hi,
It is not answered as yet.

Question 1)

This is what I am trying to do now but the error is:
Do you know why?

XQuery [Profile.Issuer.modify()]: 'text()' is not supported on simple typed or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found 'element({http://schemas.mycompany.com/myproject/[app]/stringList/1.0}:value,xs:string) *'.
------------------------------------------------

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

UPDATE p

SET Issuer.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 Profile AS p
where p.ProfileID = 1074496

Question 2)
I can not manually modify the value of the xml field in the table. How can I manually do this?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources