Convert XML from one format to another

Question

I have this below xml data which is stored in a table.

The XML Structure I have

<Response>
    <Question ID="1">
        <Value ID="1">I want a completely natural childbirth - no medical interventions for me</Value>
        <Value ID="2">no medical interventions for me</Value>
    </Question>
</Response>

I need to convert this XML to a slightly different format, like the below one.

The XML Structure I need

<Response>
    <Question ID="1">
        <SelectedChoices>
            <Choice>
                <ID>1</ID>
            </Choice>
            <Choice>
                <ID>2</ID>
            </Choice>
        </SelectedChoices>
    </Question>
</Response>

Here the "Value" is changed to "Choice" and "ID" attribute of "Value" element is changed to an element.

I know this can be done in other ways, like using an XSLT. But it will be much more helpful if can accomplish with SQL itself.

Can someone help me to convert this using SQL?

Answer 1

Use this variable to test the statements

DECLARE @xml XML=
N'<Response>
    <Question ID="1">
        <Value ID="1">I want a completely natural childbirth - no medical interventions for me</Value>
        <Value ID="2">no medical interventions for me</Value>
    </Question>
</Response>';

This can be done with FLWOR-XQuery:

The query will re-build the XML out of itself... Very similar to XSLT...

SELECT @xml.query(
N'
<Response>
{
for $q in /Response/Question
    return 
    <Question ID="{$q/@ID}">
    <SelectedChoices>
    {
    for $v in $q/Value
        return <Choice><ID>{string($v/@ID)}</ID></Choice>
    }
    </SelectedChoices>
    </Question>
}
</Response>
'
);

Another approach: Shredding and re-build

You'd reach the same with this, but I'd prefere the first...

WITH Shredded AS
(
    SELECT q.value('@ID','int') AS qID
          ,v.value('@ID','int') AS vID
    FROM @xml.nodes('/Response/Question') AS A(q)
    OUTER APPLY q.nodes('Value') AS B(v)
)
SELECT t1.qID AS [@ID]
      ,(
        SELECT t2.vID AS ID
        FROM Shredded AS t2
        WHERE t1.qID=t2.qID
        FOR XML PATH('Choice'),ROOT('SelectedChoices'),TYPE
       ) AS [node()]
FROM Shredded AS t1
GROUP BY t1.qID
FOR XML PATH('Question'),ROOT('Response')