Wrap a node around existing xml nodes using XQuery

Question

I have a large table containing an XML column, which has historical quiz data in the following format:

<quizresult>
  <question>
    <questionText>Which fire extinguisher is most suitable for a waste paper basket fire?</questionText>
    <answer number="0" value="0" chosen="0" imageURL="">Powder</answer>
    <answer number="1" value="0" chosen="0" imageURL="">Carbon Dioxide (CO2)</answer>
    <answer number="2" value="1" chosen="1" imageURL="">Water (H2O)</answer>
    <answer number="3" value="0" chosen="0" imageURL="">Foam</answer>
  </question>
  <question>
    <questionText>What should your immediate action be on hearing a fire alarm?</questionText>
    <answer number="0" value="0" chosen="0" imageURL="">Collect all your valuables</answer>
    <answer number="1" value="0" chosen="0" imageURL="">Check the weather</answer>
    <answer number="2" value="1" chosen="1" imageURL="">Leave the building by the nearest exit, closing doors behind you if the rooms are empty</answer>
  </question>
</quizresult>

Is it possible, using TSQL's XQuery, to wrap an <answers> node around the <answer...> nodes? The result would be:

<quizresult>
  <question>
    <questionText>Which fire extinguisher is most suitable for a waste paper basket fire?</questionText>
    <answers>
        <answer number="0" value="0" chosen="0" imageURL="">Powder</answer>
        <answer number="1" value="0" chosen="0" imageURL="">Carbon Dioxide (CO2)</answer>
        <answer number="2" value="1" chosen="1" imageURL="">Water (H2O)</answer>
        <answer number="3" value="0" chosen="0" imageURL="">Foam</answer>
    </answers>
  </question>
  <question>
    <questionText>What should your immediate action be on hearing a fire alarm?</questionText>
    <answers>
        <answer number="0" value="0" chosen="0" imageURL="">Collect all your valuables</answer>
        <answer number="1" value="0" chosen="0" imageURL="">Check the weather</answer>
        <answer number="2" value="1" chosen="1" imageURL="">Leave the building by the nearest exit, closing doors behind you if the rooms are empty</answer>
    </answers>
  </question>
</quizresult>

I've seen that XQuery has a replace function, shown here, but I cannot figure how to isolate the answers to put inside the function, or indeed if this is even the correct approach.

Answer 1

Small changes to an input document are often easier to achieve using XSLT rather than XQuery. In XSLT 3.0 this is:

<xsl:transform version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:mode on-no-match="shallow-copy"/>
  <xsl:template match="question">
    <question>
      <xsl:copy-of select="questionText"/>
      <answers>
        <xsl:copy-of select="answer"/>
      </answers>
    </question>
  </xsl:template>
</xsl:transform>

Of course since the data is in SQL Server you would need to export it, transform it, and then re-import it, which might end up taking longer (in both your effort and computer time) than the actual transformation.

Answer 2

I can't see an easier way of doing this than deconstructing the existing XML, and reconstructing it in the structure you would like. e.g.

DECLARE @T TABLE (ID INT IDENTITY(1, 1), X XML);
INSERT @T (X) VALUES ('<quizresult>
  <question>
    <questionText>Which fire extinguisher is most suitable for a waste paper basket fire?</questionText>
    <answer number="0" value="0" chosen="0" imageURL="">Powder</answer>
    <answer number="1" value="0" chosen="0" imageURL="">Carbon Dioxide (CO2)</answer>
    <answer number="2" value="1" chosen="1" imageURL="">Water (H2O)</answer>
    <answer number="3" value="0" chosen="0" imageURL="">Foam</answer>
  </question>
  <question>
    <questionText>What should your immediate action be on hearing a fire alarm?</questionText>
    <answer number="0" value="0" chosen="0" imageURL="">Collect all your valuables</answer>
    <answer number="1" value="0" chosen="0" imageURL="">Check the weather</answer>
    <answer number="2" value="1" chosen="1" imageURL="">Leave the building by the nearest exit, closing doors behind you if the rooms are empty</answer>
  </question>
</quizresult>');

UPDATE  t
SET     X = (   SELECT  [questionText] = t2.X.value('questionText[1]', 'NVARCHAR(MAX)'),
                        (   SELECT  [@number] = t3.X.value('@number[1]', 'INT'),
                                    [@value] = t3.X.value('@value[1]', 'INT'),
                                    [@chosen] = t3.X.value('@chosen[1]', 'INT'),
                                    [text()] = t3.X.value('text()[1]', 'NVARCHAR(MAX)')
                            FROM    t2.X.nodes('answer') AS t3 (X)
                            FOR XML PATH('answer'), TYPE
                        ) AS answers
                FROM    t.X.nodes('quizresult/question') AS t2 (X)
                FOR XML PATH('question'), ROOT('quizresult')
            ) 
FROM    @T AS t;


SELECT  *
FROM    @t;

Which will output:

<quizresult>
  <question>
    <questionText>Which fire extinguisher is most suitable for a waste paper basket fire?</questionText>
    <answers>
      <answer number="0" value="0" chosen="0">Powder</answer>
      <answer number="1" value="0" chosen="0">Carbon Dioxide (CO2)</answer>
      <answer number="2" value="1" chosen="1">Water (H2O)</answer>
      <answer number="3" value="0" chosen="0">Foam</answer>
    </answers>
  </question>
  <question>
    <questionText>What should your immediate action be on hearing a fire alarm?</questionText>
    <answers>
      <answer number="0" value="0" chosen="0">Collect all your valuables</answer>
      <answer number="1" value="0" chosen="0">Check the weather</answer>
      <answer number="2" value="1" chosen="1">Leave the building by the nearest exit, closing doors behind you if the rooms are empty</answer>
    </answers>
  </question>
</quizresult>