Use of xml.modify to insert parameters into specific element of an xml column

declare @XML xml = '<Profile>  <ID>20</ID>  <User>    <ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>    <Name>somename</Name>    <Activities>      <Activity>         <Name>activity1</Name>      </Activity>    </Activities>  </User></Profile>'declare @user_id nvarchar(50) = '20'declare @activity_name nvarchar(50) = 'activity1'declare @display_name nvarchar(50) = 'displayname1'set @xml.modify('insert <DisplayName>{sql:variable("@display_name")}</DisplayName>                 into (/Profile[ID = sql:variable("@user_id")]                       /User/Activities/                       Activity[Name = sql:variable("@activity_name")])[1]')

Result:

<Profile>  <ID>20</ID>  <User>    <ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>    <Name>somename</Name>    <Activities>      <Activity>        <Name>activity1</Name>        <DisplayName>displayname1</DisplayName>      </Activity>    </Activities>  </User></Profile>

Try this

declare @ins xml     '<Activity><Name>'+    @activity_name+    '</Name><DisplayName>'    +@display_name+    '</DisplayName></Activity>'SET @devices_xml.modify('    insert sql:variable("@ins") into (/Profile[ID=sql:variable("@user_id")]/User/Activities)[1]')

Or

SET @devices_xml.modify('      insert <Activity>             <Name>{sql:variable("@activity_name")}</Name>             <DisplayName>{sql:variable("@display_name")}</DisplayName>             </Activity>       into (/Profile[ID=sql:variable("@user_id")]/User/Activities)[1]'    )

Not sure I fully understand your logic, but you can insert into xml using sql:variable with curly brackets eg

DECLARE @profiles_xml xmlset @profiles_xml = '<Profile>  <ID>20</ID>  <User>    <ID>BC4A18CA-AFB5-4268-BDA9-C990DAFE7783</ID>    <Name>somename</Name>    <Activities>      <Activity>         <Name>activity1</Name>      </Activity>    </Activities>  </User></Profile>'SELECT 'before' s, DATALENGTH(@profiles_xml) dl, @profiles_xmlDECLARE @user_id CHAR(36), @activity_name NVARCHAR(MAX), @display_name NVARCHAR(MAX)SELECT @user_id = 'BC4A18CA-AFB5-4268-BDA9-C990DAFE7783', @activity_name = 'TEST ACTIVITY NAME', @display_name = 'TEST DISPLAY NAME'SET @profiles_xml.modify('    insert <Activity><Name>{sql:variable("@activity_name")}</Name><DisplayName>{sql:variable("@display_name")}</DisplayName></Activity>    as first    into (/Profile/User/Activities)[1]')SELECT 'after' s, DATALENGTH(@profiles_xml) dl, @profiles_xml