sdafsd, Sql Server

Language: Layout:

CREATE TABLE tbl_QuizHistory (
  HistoryId int PRIMARY KEY,
  QuizData xml NOT NULL
);
GO

INSERT INTO tbl_QuizHistory (HistoryId, QuizData) VALUES
(100, CAST(N'<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>
    <result>Correct</result>
  </question>
  <question>
    <questionText>What should your immediate action be on hearing a fire alarm?</questionText>
    <answer number="0" value="0" chosen="0" imageURL="">Find all of your colleagues before making a speedy exit together</answer>
    <answer number="1" value="0" chosen="0" imageURL="">Collect all your valuables before making a speedy exit</answer>
    <answer number="2" value="0" chosen="0" imageURL="">Check the weather to see if you need your coat before leaving</answer>
    <answer number="3" value="1" chosen="1" imageURL="">Leave the building by the nearest exit, closing doors behind you if the rooms are empty</answer>
    <result>Correct</result>
  </question>
</quizresult>' as xml)),
(101, CAST(N'<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>
    <result>Correct</result>
  </question>
  <question>
    <questionText>Should you use lifts during a fire?</questionText>
    <answer number="0" value="0" chosen="0" imageURL="">Yes</answer>
    <answer number="1" value="1" chosen="1" imageURL="">No</answer>
    <result>Correct</result>
  </question>
  <question>
    <questionText>Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?</questionText>
    <answer number="0" value="0" chosen="1" imageURL="">The body of the extinguisher</answer>
    <answer number="1" value="0" chosen="0" imageURL="">The release trigger and the bottom of the extinguisher</answer>
    <answer number="2" value="1" chosen="0" imageURL="">The horn of the extinguisher</answer>
    <result>Incorrect</result>
  </question>
</quizresult>' as xml));
GO

;WITH q AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID,
        n.q.value('(./questionText)[1]', 'nvarchar(max)') AS questionText,
        n.q.value('(./result)[1]', 'nvarchar(50)') AS result,
        n.q.query('answer') AS answers,
        answer.query('.') AS answer
    FROM tbl_QuizHistory t
    CROSS APPLY t.QuizData.nodes('/quizresult/question') AS n (q)
    CROSS APPLY n.q.nodes('answer') AS a(answer)
)
SELECT 
    q.qID,
    q.questionText,
    q.result,
    answer.value('answer[1]', 'nvarchar(max)') AS answer,
    answer.value('answer[1]/@number', 'int') AS number,
    answer.value('answer[1]/@value', 'int') AS val,
    answer.value('answer[1]/@chosen', 'bit') AS chosen
FROM q;

View schema
Execution time: 0,05 sec, rows selected: 17, rows affected: 2, absolute service time: 0,22 sec