Use Of Xml.modify To Insert Parameters Into Specific Element Of An Xml Column

I would like to use a stored procedure to insert some values passed in as parameters into elements in the xml of a column. I have this so far The following parameters: @profile_id

Solution 1:

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>

Solution 2:

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]'    )

Solution 3:

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

DECLARE@profiles_xml xml

set@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_xml

DECLARE@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