Modify XML column using MS SQL

Question

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 ?

You've asked quite a generic software problem. Whatever is generating your response can set whatever attributes it wishes to, so find out how to set an attribute in whatever XML library you're using and you're home and dry. — LordWilmore
– LordWilmore, Commented Oct 25, 2016 at 7:43
@LordWilmore Thanks for your suggestion. I have modified the code which is generating the response, so the new responses will have the ID attribute. but there are responses which are already generated before the change, will not have the attribute. So for that i have to write an update script, — NR Ganesh
– NR Ganesh, Commented Oct 25, 2016 at 8:54

Gottfried Lesigang · Accepted Answer · 2016-10-25 08:30:16Z

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

TT. · Accepted Answer · 2016-10-25 09:26:29Z

You can convert the XML to tables (question & response) and then link on the value. You then look up the ID from the question table. Example:

DECLARE @xml_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 @xml_r XML = N'
<Response>
    <Question>
        <Value>Choice B</Value>
        <Value>Choice C</Value>
    </Question>
</Response>';

;WITH q_id AS (
    SELECT
        n.c.value('(./Value)[1]','NVARCHAR(128)') AS value,
        n.c.value('@ID','INT') AS id 
    FROM
        @xml_q.nodes('//Choice') AS n(c)
),
r_v AS (
    SELECT
        n.c.value('(.)[1]','NVARCHAR(128)') AS value
    FROM
        @xml_r.nodes('//Value') AS n(c)
)
SELECT 
    (
        SELECT
            q_id.id AS "Value/@ID",
            r_v.value AS "Value"
        FROM 
            r_v 
            INNER JOIN q_id ON 
                q_id.value=r_v.value
        FOR
            XML PATH(''), TYPE
    )
FOR 
    XML PATH('Question'), ROOT('Response');

Will give the response:

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

Collectives™ on Stack Overflow

Modify XML column using MS SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related