Database Programming: Using The modify() Method Against An XML Variable


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.


Roger wrote: 



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)


as


begin


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


end


go


 


— sample call


declare @x xml;


set @x = N‘<a/>’;


exec dynamic_xml_modify N‘insert <b/> as first into (/a)[1]’, @x output;


select @x;


Thanks, Jerry, for allowing me to blog this innovative approach of yours.  Sorry it took me almost three months to post it!


     -wp

Comments (0)