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

Question

Hi all,

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

You may also try to go back to relational data and than back to xml; something like:

DECLARE @res xml = 
'<result>
  <StudentID>1</StudentID>
  <Subject>English</Subject>
  <Marks>67</Marks>
</result>
<result>
  <StudentID>1</StudentID>
  <Subject>Science</Subject>
  <Marks>75</Marks>
</result>'

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

;
WITH  cte_1
        AS ( SELECT t.c.value('StudentID[1]', 'int') AS [StudentID]
                   ,t.c.value('Subject[1]', 'varchar(50)') AS [Subject]
                   ,t.c.value('Marks[1]', 'int') AS [Marks]
             FROM   @res.nodes('/result') AS t ( c )
           ),
      cte_2
        AS ( SELECT t.c.value('StudentID[1]', 'int') AS [StudentID]
                   ,t.c.value('Name[1]', 'varchar(50)') AS [Name]
                   ,t.c.value('Roll[1]', 'int') AS [Roll]
                   ,t.c.value('Attendance[1]', 'int') AS [Attendance]
             FROM   @student.nodes('/Student') AS t ( c )
           )
  SELECT  student.StudentID
         ,student.[Name]
         ,student.Roll
         ,student.Attendance
         ,( SELECT  result.[Subject]
                   ,result.Marks
            FROM    cte_1 AS result
            WHERE   student.StudentID = result.StudentID
          FOR
            XML AUTO
               ,TYPE
               ,ELEMENTS
          )
  FROM    cte_2 AS student
FOR     XML AUTO
           ,ELEMENTS

Returns:

<student>
  <StudentID>1</StudentID>
  <Name>XYZ</Name>
  <Roll>15</Roll>
  <Attendance>50</Attendance>
  <result>
    <Subject>English</Subject>
    <Marks>67</Marks>
  </result>
  <result>
    <Subject>Science</Subject>
    <Marks>75</Marks>
  </result>
</student>

Not exactly your example, but close.

Answer 3

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.

Answer 4

    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 @ResultXML XML
SET @ResultXML = CAST(CAST(@student AS VARCHAR(MAX)) + '<Result>'
    + CAST(@res AS VARCHAR(MAX)) + '</Result>' AS XML)
SET @ResultXML.modify('insert /*[2]/* as first into (/Student/Result)[1]')
SET @ResultXML.modify('delete /*[2] ')
SELECT  @ResultXML