Xquery Insert method, argument using variables, in SQL Server, string literal error

I am working in a SQL Server 2008 environment.

Within a Stored Procedure, I am trying to write a XQuery Modify.Insert statement against an XML datatype field in a Table, where the Insert argument includes variables, for example:

update Table1
set XMLdata.modify(
'insert <Supplier SupplierID="' + @supplierid +
'" Name="' + @suppliername +
'"><Address Addr1="' + @addr +
'" City="' + @city +
'" State="' + @stateabbrev +
'" Zip="' + @zip + '" /></Supplier>
 as last into
 (/BookStore/Book[./@BookID="' + @bookid + '"]/Suppliers)[1])')

Executing this results in error message "xml data type method "modify" must be a string literal."

I have already attempted to work this out in by the following alternate methods, but they have not resulted in errors I cannot resolve either:

1) instead of a concatenated string statement like the above, I tried using the sql variable function,   (example:  ...SupplierID="sql:variable("@supplierid")"   ), but this also gave errors I couldn't resolve.

2) I tried assign a String variable the ENTIRE Update statement, and then execute that (e.g. Exec(@string), but this also errors - I believe because there are too many hierarchies of strings within strings - I see at least three - don't know how to properly use " or ' for the 3rd nesting level.

Can anyone provide guidance?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
The "string literal" message you get is saying that you need to pass fixed string to the modify function (i.e. no concatenation). This is typically achieved with the sql:variable function, as you said you tried. I do not see the curly braces surrounding your example, though, for the sql:variable function. Could you perhaps try the following?
update Table1
set XMLdata.modify(
'insert <Supplier SupplierID="{sql:variable("@supplierid")}" Name="{sql:variable("@suppliername")}"><Address Addr1="{sql:variable("@addr")}" City="{sql:variable("@city")}" State="{sql:variable("@stateabbrev")}" Zip="{sql:variable("@zip")}" /></Supplier>
 as last into (/BookStore/Book[./@BookID="{sql:variable("@bookid")}]/Suppliers)[1])')

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
madmaremtnAuthor Commented:
kaufmed - I tried the code you supplied, but got the following error:

Msg 2217, Level 16, State 1, Line 105
XQuery [db_Assignment1.XMLAssignment4.XML.modify()]: ',' or ')' expected

It looks like there is an unclosed " mark in your code above (starting at  ...@BookID="{...  ), however, I got the same error when I made some edits to account for this.

Anthony PerkinsCommented:
You left a trailing paranthesis at the very end.  Try it this way:
Declare @Xmldata XML

SET @XMLdata.modify( 
	'insert <Supplier 
		as last into 

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.