How to insert xml into a node in another xml using XQuery?

Question

I have two xml variable say @res, @student in a stored proc in SQL server 2005.

@res contains

<Subject>English</Subject>
<Marks>67</Marks>
<Subject>Science</Subject>
<Marks>75</Marks>

@student contains:

<Student> 
   <Name>XYZ</Name>
   <Roll>15</Roll>
   <Result />
   <Attendance>50</Attendance>
</Student>

I need to insert the xml of @res into the node Result in @student variable using XQuery.

How to implement that?

Please help.

Answer 1

In SQL Server 2008, it's pretty easy:

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

That gives me the output:

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

Unfortunately, the ability to call .modify() and use a sql:variable in the insert statement was introduced with SQL Server 2008 only - doesn't work in SQL Server 2005.

I don't see how you could do this in SQL Server 2005, other than resorting back to ugly string parsing and replacement:

SET @student = 
    CAST(REPLACE(CAST(@student AS VARCHAR(MAX)), 
                 '<Result/>', 
                 '<Result>' + CAST(@res AS VARCHAR(MAX)) + '</Result>') AS XML)

Marc