CODEWITHSUNDEEP
sqlxmlt-sqlxpathxquery
NR Ganesh
NR Ganesh

Reputation: 111

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 ?

Upvotes: 1

Views: 70

Answers (2)

TT.
TT.

Reputation: 16146

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>

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

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

Upvotes: 2

Related Questions