如何使用XQuery将xml插入到另一个xml的节点中？

Question

在server 2005中存储的proc中有两个xml变量say @res，@say。

@res包含

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

@学生包括：

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

我需要使用@将@res的xml插入到@变量中的节点XQuery中。

如何实现这一点？

请帮帮忙。

Answer 1

在Server 2008中，这非常简单：

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

这给了我输出：

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

不幸的是，仅在Server 2008中引入了调用.modify()并在insert语句中使用sql:variable的能力--在Server 2005中不起作用。

除了回到丑陋的字符串解析和替换之外，我看不出如何在Server 2005中做到这一点：

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

Marc

Answer 2

这将适用于SQL 2005，并且主要是一种xquery解决方案：

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

如果需要的话，您可以在这一点上将你的“学生变量”设置为“final”。节点的"test“名称正是我选择使用的名称。您可以使用任何名称，只要它不会出现在XML中。

基本上，您只需将两个XML字符串放在一起，这样xquery就可以同时使用它们。

Answer 3

您还可以尝试返回关系数据，而不是返回xml；如下所示：

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

返回：

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

不完全是你的例子，但很接近。