title: SQL Server append XML child nodes to parent node
body: <p>I need to have a script which can insert / append new xml child nodes to a pre-existing xml parent node.</p>
  
  <pre><code>--New child nodes
  DECLARE @XMLChildData XML
  SET @XMLChildData = '
  &lt;Persons&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Gary&lt;/Firstname&gt;
          &lt;Surname&gt;Smith&lt;/Surname&gt;
          &lt;Telephone&gt;0115547899&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;1 Church Lane&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Rosebank&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Houghton&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Wayne&lt;/Firstname&gt;
          &lt;Surname&gt;Farmey&lt;/Surname&gt;
          &lt;Telephone&gt;0117453269&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;51 Oak Street&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Rivionia&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Sandton&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Mark&lt;/Firstname&gt;
          &lt;Surname&gt;Jones&lt;/Surname&gt;
          &lt;Telephone&gt;0119854741&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;4 Arum Lane&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Glen Hazel&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Johannesburg&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
  &lt;/Persons&gt;'
  
  --Existing parent node
  DECLARE @XMLParentData XML
  SET @XMLParentData = '
  &lt;Persons&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Sarah&lt;/Firstname&gt;
          &lt;Surname&gt;Gray&lt;/Surname&gt;
          &lt;Telephone&gt;0113265874&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;78 Emerl Aveune&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Fourways&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Sandton&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Jenna&lt;/Firstname&gt;
          &lt;Surname&gt;Reed&lt;/Surname&gt;
          &lt;Telephone&gt;0114781102&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;6 Park Lane&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Parkhurst&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Rosebank&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Mike&lt;/Firstname&gt;
          &lt;Surname&gt;Wilke&lt;/Surname&gt;
          &lt;Telephone&gt;0116532003&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;22 High Road&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Modderfontein&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Edenvale&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
  &lt;/Persons&gt;'
  </code></pre>
  
  <p>I want the end result to be:</p>
  
  <pre><code>&lt;Persons&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Sarah&lt;/Firstname&gt;
          &lt;Surname&gt;Gray&lt;/Surname&gt;
          &lt;Telephone&gt;0113265874&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;78 Emerl Aveune&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Fourways&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Sandton&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Jenna&lt;/Firstname&gt;
          &lt;Surname&gt;Reed&lt;/Surname&gt;
          &lt;Telephone&gt;0114781102&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;6 Park Lane&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Parkhurst&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Rosebank&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Mike&lt;/Firstname&gt;
          &lt;Surname&gt;Wilke&lt;/Surname&gt;
          &lt;Telephone&gt;0116532003&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;22 High Road&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Modderfontein&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Edenvale&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Gary&lt;/Firstname&gt;
          &lt;Surname&gt;Smith&lt;/Surname&gt;
          &lt;Telephone&gt;0115547899&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;1 Church Lane&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Rosebank&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Houghton&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Wayne&lt;/Firstname&gt;
          &lt;Surname&gt;Farmey&lt;/Surname&gt;
          &lt;Telephone&gt;0117453269&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;51 Oak Street&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Rivionia&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Sandton&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
      &lt;Person&gt;
          &lt;Firstname&gt;Mark&lt;/Firstname&gt;
          &lt;Surname&gt;Jones&lt;/Surname&gt;
          &lt;Telephone&gt;0119854741&lt;/Telephone&gt;
          &lt;Address&gt;
              &lt;AddressLine&gt;4 Arum Lane&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Glen Hazel&lt;/AddressLine&gt;
              &lt;AddressLine&gt;Johannesburg&lt;/AddressLine&gt;
              &lt;AddressLine&gt;South Africa&lt;/AddressLine&gt;
          &lt;/Address&gt;
      &lt;/Person&gt;
  &lt;/Persons&gt;
  </code></pre>
  
  <p>I know i need to use the .modify(), however i am not sure how to iterate through the child nodes and insert / append each child <code>"&lt;person&gt;"</code> node into the parent <code>"&lt;persons&gt;"</code> node.</p>
  
  <p>I would think it would need to be something similiar as below</p>
  
  <pre><code>SET @XMLParentData.modify('
      insert     
          (
              sql:variable("@XMLChildData")
          )
      after
          (/Person[1]/Person[1])
  ')
  
  SELECT @XMLData
  </code></pre>