• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3116
  • Last Modified:

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?

2 Solutions
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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now