How do you insert xml into a node in another xml using XQuery? This post discussed that.

This example is from the website stackoverflow.com.

-- How to insert xml into a node in another xml using XQuery?
-- http://stackoverflow.com/questions/1886565/how-to-insert-xml-into-a-node-in-another-xml-using-xquery 
DECLARE @res XML = '<Subject>English</Subject>
<Marks>67</Marks>
<Subject>Science</Subject>
<Marks>75</Marks>'
DECLARE @student XML = '<Student> 
   <Name>XYZ</Name>
   <Roll>15</Roll>
   <Result />
   <Attendance>50</Attendance>
</Student>'
SET @student.modify('insert sql:variable("@res") as first into (/Student/Result)[1]')
SELECT @student
/*
The result of the above SELECT gives the following
<Student>
  <Name>XYZ</Name>
  <Roll>15</Roll>
  <Result>
    <Subject>English</Subject>
    <Marks>67</Marks>
    <Subject>Science</Subject>
    <Marks>75</Marks>
  </Result>
  <Attendance>50</Attendance>
</Student>
The ability to call .modify() and use a sql:variable in the insert statement
was introduced with SQL Server 2008 
*/

Part of a Project

Suppose you had a project and part of the requirements was to produce and xml file from two tables. From tables to xml variables, from xml variables to combining into one xml variable, and from one xml variable to an external xml file. Also, once you have the one xml variable, you need to keep a record of that, so you need to store the xml variable into a table.

For a post that discusses getting data from regular SQL Server tables into XML variables have a look at the post called SQL Server Table to XML Variable.