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