SQL Server Insert New XML Node at the End

 in SQL Server by

This post is a simple illustration of insert an empty node at the end of an existing node. This post is based on a post at sqlservercentral.com. Below is the XML we are starting with, assigned to a variable in SQL Server. 

DECLARE @TXML XML = '
<Attribute>
  <A1>6529</A1>
  <A2>6529</A2>
</Attribute>';

Here is what we want to end up with. 

<Attribute>
  <A1>6529</A1>
  <A2>6529</A2>
  <A3 />
</Attribute>

Here is the SQL code that accomplishes our goal. 

DECLARE @TXML XML = '
<Attribute>
  <A1>6529</A1>
  <A2>6529</A2>
</Attribute>';

DECLARE @NNODE  VARCHAR(100) = 'A3';
DECLARE @NVAL   VARCHAR(100) = '';
DECLARE @Nt     VARCHAR(200) = '<{{@NNODE}}>{{@NVAL}}</{{@NNODE}}>';
DECLARE @NXML   XML;
SELECT @NXML = CONVERT(XML,REPLACE(REPLACE(@NT,'{{@NNODE}}',@NNODE),'{{@NVAL}}',@NVAL),0);
SET @TXML.modify('insert sql:variable("@NXML") as last into (/Attribute)[1]');
SELECT @TXML;