SQL 如何使用 XQuery 将 xml 插入另一个 xml 中的节点？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1886565/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert xml into a node in another xml using XQuery?
提问by Himadri
I have two xml variable say @res, @student in a stored proc in SQL server 2005.
我在 SQL Server 2005 的存储过程中有两个 xml 变量，比如 @res、@student。
@res contains
@res 包含
<Subject>English</Subject>
<Marks>67</Marks>
<Subject>Science</Subject>
<Marks>75</Marks>
@student contains:
@student 包含：
<Student>
<Name>XYZ</Name>
<Roll>15</Roll>
<Result />
<Attendance>50</Attendance>
</Student>
I need to insert the xml of @res into the node Resultin @student variable using XQuery.
我需要使用 XQuery将 @res 的 xml 插入到@student 变量中的节点结果中。
How to implement that?
如何实施？
Please help.
请帮忙。
回答by marc_s
In SQL Server 2008, it's pretty easy:
在 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
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:variablein the insert statement was introduced with SQL Server 2008 only - doesn't work in SQL Server 2005.
不幸的是，在插入语句中调用.modify()和使用 a 的功能仅sql:variable在 SQL Server 2008 中引入 - 在 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:
除了求助于丑陋的字符串解析和替换之外，我不知道您如何在 SQL Server 2005 中执行此操作：
SET @student =
CAST(REPLACE(CAST(@student AS VARCHAR(MAX)),
'<Result/>',
'<Result>' + CAST(@res AS VARCHAR(MAX)) + '</Result>') AS XML)
Marc
马克
回答by Tom H
This will work in SQL 2005 and is mostly an xquery solution:
这将适用于 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
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.
如果需要，您可以在那时将 @student 变量设置为 @final。节点的“测试”名称正是我选择使用的名称。您可以使用任何名称，只要它尚未出现在您的 XML 中即可。
You basically just throw the two XML strings together so that they are both available to xquery at once.
您基本上只是将两个 XML 字符串放在一起，以便 xquery 可以同时使用它们。
回答by Damir Sudarevic
You may also try to go back to relational data and than back to xml; something like:
您也可以尝试回到关系数据而不是回到 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>
Not exactly your example, but close.
不完全是你的例子，但很接近。
