Ganesh Rajan Ganesh Rajan - 1 month ago 7
SQL Question

Modify XML column using MS SQL

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 ?

Answer

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