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

Question

我有两个 xml 变量，比如 SQL Server 2005 中的存储过程中的 @res、@student。

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

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

如何实施？

请帮忙。

Answer 1

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

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

除了求助于丑陋的字符串解析和替换之外，我不知道如何在 SQL 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

如果需要的话，您可以在此时将 @student 变量设置为 @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

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>

不完全是您的示例，但很接近。