title: Use of xml.modify to insert parameters into specific element of an xml column
body: <p>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:</p>
  
  <pre><code>@profile_id int,
  @user_id nvarchar(50),
  @activity_name nvarchar(50),
  @display_name nvarchar(50)
  </code></pre>
  
  <p>Retrieve the desired xml:</p>
  
  <pre><code>DECLARE @profiles_xml xml
  SET @profiles_xml = (SELECT profiles from tbl_applied_profiles WHERE profiles.value('(Profile/ID)[1]','int')= @profile_id)
  </code></pre>
  
  <p>The xml from the column inside the @profiles_xml looks like this:</p>
  
  <pre><code>&lt;Profile&gt;
    &lt;ID&gt;20&lt;/ID&gt;
    &lt;User&gt;
      &lt;ID&gt;BC4A18CA-AFB5-4268-BDA9-C990DAFE7783&lt;/ID&gt;
      &lt;Name&gt;somename&lt;/Name&gt;
      &lt;Activities&gt;
        &lt;Activity&gt;
           &lt;Name&gt;activity1&lt;/Name&gt;
        &lt;/Activity&gt;
      &lt;/Activities&gt;
    &lt;/User&gt;
  &lt;/Profile&gt;
  </code></pre>
  
  <p>Attempt to Insert into User with specific ID the activity name and display name:</p>
  
  <pre><code>SET @profiles_xml.modify('
      insert
      if(/Profile/User/ID=sql:variable("@user_id"))
      then Activity[Name=sql:variable("@activity_name")][DisplayName=sql:variable("@display_name")]
      else()
      as first
      into (/Profile/User/Activities)[1]')
  </code></pre>
  
  <p>I have also tried this with no success:</p>
  
  <pre><code> SET @devices_xml.modify('
      insert /Profile/User[ID=sql:variable("@user_id")]/Activity[Name=sql:variable("@activity_name")][DisplayName=sql:variable("@display_name")]
      into (/Profile/User/Activities)[1]')
  </code></pre>
  
  <p>And this:</p>
  
  <pre><code> SET @devices_xml.modify('
  insert
   /Profile/User[ID=sql:variable("@user_id")]/Activities/Activity[Name=sql:variable("@activity_name")][DisplayName=sql:variable("@display_name")]
      into (/Profile/User/Activities)[1]')
  </code></pre>
  
  <p>What is the correct way to do this?</p>