SQL:in XML modify() statement, can you use variable for tag name?

Question

I'm trying to do something like:

SET @xml.modify ('insert <@x)>@y into (/something)[ID="@id"][1]')

I figured out when I replace @y with {sql:variable("@y")}, it worked. But it doesn't work for @x.

Is there a way to make it work?

Answer 1

It is not possible to insert an element with a dynamic name. Look at these examples:

DECLARE @x XML=
N'<root>
<test>Some test value</test>
</root>';

Easy going, everything fixed literals

SET @x.modify('insert <blah>new element</blah> as first into (/root)[1]')
SELECT @x;

The result

<root>
  <blah>new element</blah>
  <test>Some test value</test>
</root>

Dynamic content

DECLARE @content NVARCHAR(100)='dynamic content'
SET @x.modify('insert <blah>{sql:variable("@content")}</blah> as first into (/root)[1]');
SELECT @x;

The result

<root>
  <blah>dynamic content</blah>
  <blah>new element</blah>
  <test>Some test value</test>
</root>

This is not allowed and will lead to an error

DECLARE @element NVARCHAR(100)='dynElement';
SET @x.modify('insert <{sql:variable("@element")}>{sql:variable("@content")}</{sql:variable("@element")}> as first into (/root)[1]');
SELECT @x;

But you can create the full XML-element externally and insert it as-is

DECLARE @new_element XML='<dynElement>Some dynamic content</dynElement>';
SET @x.modify('insert sql:variable("@new_element") as first into (/root)[1]');
SELECT @x;

The result

<root>
  <dynElement>Some dynamic content</dynElement>
  <blah>dynamic content</blah>
  <blah>new element</blah>
  <test>Some test value</test>
</root>

This would lead to the same result

DECLARE @new_element XML=(SELECT 'Some dynamic content' FOR XML PATH('dynElement'));
SET @x.modify('insert sql:variable("@new_element") as first into (/root)[1]');
SELECT @x;

And this - fully parameterised - would also return the same

DECLARE @element NVARCHAR(100)='dynElement';
DECLARE @content NVARCHAR(100)='dynamic content'

DECLARE @new_element XML='<' + @element +'>' +  @content + '</' +  @element + '>';

SET @x.modify('insert sql:variable("@new_element") as first into (/root)[1]');
SELECT @x;