#8172 [error] need help

-- The following code throw exception~

-- Need HELP!!

use AdventureWorksgo--drop table T--gocreate table T(ProductModelIDint primary key, Instructionsxml (Production.ManuInstructionsSchemaCollection))goinsert Tselect ProductModelID, Instructionsfrom Production.ProductModelwhere ProductModelID=7goselect Instructionsfrom T--1) insert <Location 1000/>. Note: <Root> must be singleton in the queryupdate Tset Instructions.modify(' declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; insert <MI:Location LocationID="1000" LaborHours="1000" > These are manu steps at location 1000. <MI:step>New step1 instructions</MI:step> Instructions for step 2 are here <MI:step>New step 2 instructions</MI:step> </MI:Location> as first into (/MI:root)[1]')goselect Instructionsfrom Tdeclare @.aaaaaintset @.aaaaa=1000-- delete an attributeupdate Tset Instructions.modify(' declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions"; delete(/MI:root/MI:Location[@.LocationID='+@.aaaaa+']/@.LaborHours) ')--,--Instructions.modify('-- declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";-- delete(/MI:root/MI:Location[@.LocationID='+@.aaaaa+']/text()) --'),--Instructions.modify('-- declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";-- delete(/MI:root/MI:Location[@.LocationID='+@.aaaaa+']/MI:step[2]) --')goselect Instructionsfrom T-- cleanupdrop table Tgo

That means you can't use dynamic built string as the argument of modify method. You needsql:variable("@.var") function, for example:

update T
set Instructions.modify('
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
delete(/MI:root/MI:Location[@.LocationID=sql:variable("@.aaaaa")]/@.LaborHours)
')

To learn more about XQuery in SQL2005, you can refer to:

http://msdn.microsoft.com/library/en-us/dnsql90/html/sql2k5_xqueryintro.asp?frame=true

|||

Thx very very very very much