使用MS SQL修改XML列(Modify XML column using MS SQL)

使用MS SQL修改XML列(Modify XML column using MS SQL)

我有这两个XML存储在2个表中。

问题XML

<Question> <Choice ID="1"> <Value>Choice A</Value> </Choice> <Choice ID="2"> <Value>Choice B</Value> </Choice> <Choice ID="3"> <Value>Choice C</Value> </Choice> <Choice ID="4"> <Value>Choice D</Value> </Choice> <Choice ID="5"> <Value>Choice E</Value> </Choice> </Question>

响应XML

<Response> <Question> <Value>Choice B</Value> <Value>Choice C</Value> </Question> </Response>

我需要向Response XML中的所有Value元素添加一个名为ID的新属性 。 可以在问题XML中找到新ID属性的值 。

对于Instance，如果您看到Question XML ，则值Choice B is 2的正确ID Choice B is 2而Choice C is 3

所以我需要的最终Response XML应该是这样的

<Response> <Question> <Value ID="2">Choice B</Value> <Value ID="3">Choice C</Value> </Question> </Response>

有人可以告诉我该怎么做？

I've these 2 XML which is stored in 2 tables.

Question XML

<Question> <Choice ID="1"> <Value>Choice A</Value> </Choice> <Choice ID="2"> <Value>Choice B</Value> </Choice> <Choice ID="3"> <Value>Choice C</Value> </Choice> <Choice ID="4"> <Value>Choice D</Value> </Choice> <Choice ID="5"> <Value>Choice E</Value> </Choice> </Question>

Response XML

<Response> <Question> <Value>Choice B</Value> <Value>Choice C</Value> </Question> </Response>

I need to add a new attribute called ID, to all the Value elements present in the Response XML. The value of the new ID attribute can be found in the Question XML.

For Instance, If you see the Question XML, the correct ID of the value Choice B is 2 and Choice C is 3

So the final Response XML which i need, should be like this

<Response> <Question> <Value ID="2">Choice B</Value> <Value ID="3">Choice C</Value> </Question> </Response>

Can someone please tell me how to do this ?

最满意答案

如果要在大多数情况下修改XML中的多个位置，最好是粉碎信息并从头开始重新构建XML：

DECLARE @q XML= N'<Question> <Choice ID="1"> <Value>Choice A</Value> </Choice> <Choice ID="2"> <Value>Choice B</Value> </Choice> <Choice ID="3"> <Value>Choice C</Value> </Choice> <Choice ID="4"> <Value>Choice D</Value> </Choice> <Choice ID="5"> <Value>Choice E</Value> </Choice> </Question>'; DECLARE @r XML= N'<Response> <Question> <Value>Choice B</Value> <Value>Choice C</Value> </Question> </Response>'; WITH QuestionCTE AS ( SELECT c.value('@ID','int') AS qID ,c.value('Value[1]','nvarchar(max)') AS qVal FROM @q.nodes('Question/Choice') AS A(c) ) ,ResponseCTE AS ( SELECT r.value('.','nvarchar(max)') AS rVal FROM @r.nodes('Response/Question/Value') AS A(r) ) SELECT ( SELECT q.qID AS [Value/@ID] ,q.qVal AS [Value] FROM ResponseCTE AS r LEFT JOIN QuestionCTE AS q ON r.rVal=q.qVal FOR XML PATH(''),TYPE ) FOR XML PATH('Question'),ROOT('Response')

If you want to modify more than one place in an XML in most cases the best is to shredd the information and re-build the XML from scratch:

DECLARE @q XML= N'<Question> <Choice ID="1"> <Value>Choice A</Value> </Choice> <Choice ID="2"> <Value>Choice B</Value> </Choice> <Choice ID="3"> <Value>Choice C</Value> </Choice> <Choice ID="4"> <Value>Choice D</Value> </Choice> <Choice ID="5"> <Value>Choice E</Value> </Choice> </Question>'; DECLARE @r XML= N'<Response> <Question> <Value>Choice B</Value> <Value>Choice C</Value> </Question> </Response>'; WITH QuestionCTE AS ( SELECT c.value('@ID','int') AS qID ,c.value('Value[1]','nvarchar(max)') AS qVal FROM @q.nodes('Question/Choice') AS A(c) ) ,ResponseCTE AS ( SELECT r.value('.','nvarchar(max)') AS rVal FROM @r.nodes('Response/Question/Value') AS A(r) ) SELECT ( SELECT q.qID AS [Value/@ID] ,q.qVal AS [Value] FROM ResponseCTE AS r LEFT JOIN QuestionCTE AS q ON r.rVal=q.qVal FOR XML PATH(''),TYPE ) FOR XML PATH('Question'),ROOT('Response')