Here’s the first technical tid-bit on this blog in awhile, courtesy of a long-concluded internal conversation that I rediscovered when I was cleaning out three months’ worth of email from my inbox.
I’m trying to use the modify() method against an XML variable, and I keep getting “must be a string literal” errors. Any suggestions?
While it’s true that native T-SQL doesn’t currently support this scenario, Jerry came to the rescue with an approach utilizing parameterized dynamic SQL with output parameters. Here’s Jerry’s stored procedure, along with a sample call:
— procedure to call modify() method
create procedure dynamic_xml_modify(@xquery nvarchar(max), @x xml output)
declare @sql nvarchar(max);
— quote ‘ characters to prevent sql injection
set @sql = N‘set @p.modify(”’ + replace(@xquery, N””, N”””) + ”’);’
exec sp_executesql @sql, N‘@p xml output’, @p = @x output
— sample call
declare @x xml;
set @x = N‘<a/>’;
exec dynamic_xml_modify N‘insert <b/> as first into (/a)’, @x output;
Thanks, Jerry, for allowing me to blog this innovative approach of yours. Sorry it took me almost three months to post it!