SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tbl_QuizHistory (
  HistoryId int PRIMARY KEY,
  QuizData xml NOT NULL
);
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));
2 rows affected

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


HistoryId
qID
questionText
result
answerNbr
answerChosen
answerTxt


100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
0
False
Powder

100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
1
False
Carbon Dioxide (CO2)

100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
2
True
Water (H2O)

100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
3
False
Foam

100
2
What should your immediate action be on hearing a fire alarm?
Correct
0
False
Find all of your colleagues before making a speedy exit together

100
2
What should your immediate action be on hearing a fire alarm?
Correct
1
False
Collect all your valuables before making a speedy exit

100
2
What should your immediate action be on hearing a fire alarm?
Correct
2
False
Check the weather to see if you need your coat before leaving

100
2
What should your immediate action be on hearing a fire alarm?
Correct
3
True
Leave the building by the nearest exit, closing doors behind you if the rooms are empty

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
0
False
Powder

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
1
False
Carbon Dioxide (CO2)

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
2
True
Water (H2O)

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
3
False
Foam

101
2
Should you use lifts during a fire?
Correct
0
False
Yes

101
2
Should you use lifts during a fire?
Correct
1
True
No

101
3
Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?
Incorrect
0
True
The body of the extinguisher

101
3
Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?
Incorrect
1
False
The release trigger and the bottom of the extinguisher

101
3
Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?
Incorrect
2
False
The horn of the extinguisher


 SELECT HistoryId, 
        t.qID,
        t.questionText,
        t.result,
        a.aId,
        a.answerNbr,
        a.answerChosen,
        a.answerTxt
    FROM
        tbl_QuizHistory
    CROSS APPLY QuizData.nodes('quizresult') AS n(q)    
    CROSS APPLY (
        SELECT 
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID,
          t.q.value('(./questionText)[1]', 'nvarchar(max)') AS questionText,
          t.q.value('(./result)[1]', 'nvarchar(50)') AS result,
          t.q.query('.') queryXml
        FROM 
          n.q.nodes('./question') t(q)
    ) t
    CROSS APPLY (
        SELECT 
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS aID,
          q.a.value('(./@number)[1]', 'int') as answerNbr,
          q.a.value('(./@chosen)[1]', 'bit') as answerChosen,
          q.a.value('.','nvarchar(max)') as answerTxt
        FROM
          t.queryXml.nodes('question/answer') q(a)
   ) a



HistoryId
qID
questionText
result
aId
answerNbr
answerChosen
answerTxt


100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
1
0
False
Powder

100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
2
1
False
Carbon Dioxide (CO2)

100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
3
2
True
Water (H2O)

100
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
4
3
False
Foam

100
2
What should your immediate action be on hearing a fire alarm?
Correct
1
0
False
Find all of your colleagues before making a speedy exit together

100
2
What should your immediate action be on hearing a fire alarm?
Correct
2
1
False
Collect all your valuables before making a speedy exit

100
2
What should your immediate action be on hearing a fire alarm?
Correct
3
2
False
Check the weather to see if you need your coat before leaving

100
2
What should your immediate action be on hearing a fire alarm?
Correct
4
3
True
Leave the building by the nearest exit, closing doors behind you if the rooms are empty

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
1
0
False
Powder

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
2
1
False
Carbon Dioxide (CO2)

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
3
2
True
Water (H2O)

101
1
Which fire extinguisher is most suitable for a waste paper basket fire?
Correct
4
3
False
Foam

101
2
Should you use lifts during a fire?
Correct
1
0
False
Yes

101
2
Should you use lifts during a fire?
Correct
2
1
True
No

101
3
Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?
Incorrect
1
0
True
The body of the extinguisher

101
3
Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?
Incorrect
2
1
False
The release trigger and the bottom of the extinguisher

101
3
Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?
Incorrect
3
2
False
The horn of the extinguisher

HistoryId	qID	questionText	result	answerNbr	answerChosen	answerTxt
100	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	0	False	Powder
100	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	1	False	Carbon Dioxide (CO2)
100	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	2	True	Water (H2O)
100	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	3	False	Foam
100	2	What should your immediate action be on hearing a fire alarm?	Correct	0	False	Find all of your colleagues before making a speedy exit together
100	2	What should your immediate action be on hearing a fire alarm?	Correct	1	False	Collect all your valuables before making a speedy exit
100	2	What should your immediate action be on hearing a fire alarm?	Correct	2	False	Check the weather to see if you need your coat before leaving
100	2	What should your immediate action be on hearing a fire alarm?	Correct	3	True	Leave the building by the nearest exit, closing doors behind you if the rooms are empty
101	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	0	False	Powder
101	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	1	False	Carbon Dioxide (CO2)
101	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	2	True	Water (H2O)
101	1	Which fire extinguisher is most suitable for a waste paper basket fire?	Correct	3	False	Foam
101	2	Should you use lifts during a fire?	Correct	0	False	Yes
101	2	Should you use lifts during a fire?	Correct	1	True	No
101	3	Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?	Incorrect	0	True	The body of the extinguisher
101	3	Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?	Incorrect	1	False	The release trigger and the bottom of the extinguisher
101	3	Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating?	Incorrect	2	False	The horn of the extinguisher