How to convert XML data with attributes to SQL Row, Sql Server

Language: Layout:

create table Tests (testid int, Questionnaire xml);
insert into Tests values (1, '
<Questions>
  <Question ID="1" Prompt="What colour is the sky?" CorrectAnswerID="22">
    <Answers>
      <Answer ID="22" Prompt="blue" />
      <Answer ID="23" Prompt="green" />
      <Answer ID="24" Prompt="pink" />
      <Answer ID="25" Prompt="brown" />
    </Answers>
  </Question>
  <Question ID="1" Prompt="What colour is the ocean?" CorrectAnswerID="22">
    <Answers>
      <Answer ID="22" Prompt="blue" />
      <Answer ID="23" Prompt="green" />
      <Answer ID="24" Prompt="pink" />
      <Answer ID="25" Prompt="brown" />
    </Answers>
  </Question>
</Questions>');
SELECT t.testid, 
    a.c.value('../../@ID', 'varchar(max)') as Questionid,
    a.c.value('../../@Prompt', 'varchar(max)') as Questionsprompt,
    a.c.value('../../@CorrectAnswerID', 'varchar(max)') as CorrectAnswerID,
    a.c.value('@ID', 'varchar(max)') as AnswerID,
    a.c.value('@Prompt', 'varchar(max)') as AnswerOption
from Tests as t
CROSS apply t.Questionnaire.nodes('Questions/Question/Answers/Answer') as a(c)

View schema
Execution time: 0,08 sec, rows selected: 8, rows affected: 1, absolute service time: 0,27 sec, absolute service time: 0,27 sec