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

Answer 2

This will work in SQL 2005 and is mostly an xquery solution:

DECLARE @res xml

SET @res = 
'<Subject>English</Subject>
<Marks>67</Marks>
<Subject>Science</Subject>
<Marks>75</Marks>'

DECLARE @student xml
SET @student =
'<Student>
   <Name>XYZ</Name>
   <Roll>15</Roll>
   <Result />
   <Attendance>50</Attendance>
</Student>'

DECLARE @final XML

SET @final = CAST(CAST(@student AS VARCHAR(MAX)) + '<test>' + CAST(@res AS VARCHAR(MAX)) + '</test>' AS XML)

SET @final.modify('insert /test/* into (/Student/Result)[1]')
SET @final.modify('delete /test')

SELECT @final

You can set your @student variable to @final at that point if you need to do that. The name of "test" for the node was just what I chose to use. You can use any name as long as it will not already appear in your XML.

You basically just throw the two XML strings together so that they are both available to xquery at once.