December 26, 2008 at 5:43 am
could any one tell me what is wrong in the following script:
when I'm running this script it gives me following error message:
Msg 2224, Level 16, State 1, Line 19
XQuery [modify()]: An expression was expected
Script:
-------
declare @s-2 xml
set @s-2='<student>
<name>raj</name>
<rno>10</rno>
<branch>ECE</branch>
<college>JITS</college>
</student>'
declare @a varchar(10)
set @a='xyz'
-- works correctly
set @S.modify('insert element section {sql:variable("@a")} after (student/college)[1]')
select @s-2
-- gives above error message
set @S.modify('replace value of (/student/name/text())[1] with {sql:variable("@a")}')
select @s-2
Regards,
-Kiran
December 26, 2008 at 6:01 am
You have a small typo in your second update statement. You had one extra { sign. Take it out and it will work
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 27, 2008 at 6:40 am
sorry guys! It was my fault. I have updated the script and added the closing } brace, but still I am getting the same error message. Could you tell me what is wrong in that script.
Regards,
-Kiran
December 27, 2008 at 7:15 am
You edited your original post and you've added the closing brackets. Instead you need to remove the brackets. Bellow is the script that I ran with no errors:
declare @s-2 xml
set @s-2='
'
declare @a varchar(10)
set @a='xyz'
-- works correctly
set @S.modify('insert element section {sql:variable("@a")} after (student/college)[1]')
select @s-2
-- works correctly
set @S.modify('replace value of (/student/name/text())[1] with sql:variable("@a")')
select @s-2
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 27, 2008 at 10:56 pm
wow, thanks Adi..It works perfect...::). But I have one doubt, why it is accepting braces at insertion and why can't in "replacing value of" statement? does it mean by design the functionality has implemented like that? any idea?
Regards,
-Kiran
December 27, 2008 at 11:15 pm
Sorry, but I really have no idea about this one.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply