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:
'insert <Supplier SupplierID="' + @supplierid +
'" Name="' + @suppliername +
'"><Address Addr1="' + @addr +
'" City="' + @city +
'" State="' + @stateabbrev +
'" Zip="' + @zip + '" /></Supplier>
as last into
(/BookStore/Book[./@BookID="' + @bookid + '"]/Suppliers))')
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?