如何将具有属性的 XML 数据转换为 SQL 行

问：

我需要将 XML 数据转换为 SQL 表，从而将 XML 数据包含在 SQL 表的列中。 XML 数据包含多个属性和节点。

XML 数据由具有多项选择答案的问题组成。每个测试都有不同数量的问题，每个问题可以有不同数量的多项选择选项。每个问题只有一个正确答案。

我已经阅读了几个关于如何将 XML 数据引入 SQL 表的网站，但是我似乎无法为我的特定问题找到解决方案，因为 XML 数据非常复杂（至少对我来说没有太多经验）

我使用的表格：

表名称：Tests

列：

TestID - 1
Name - "Test"
Description - "This is a test"
...
Questionnaire - Containing the XML data

调查问卷列中的 XML 数据如下所示：

<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>

对于此 XML 列中的每个问题，将重复这组代码。

我试图用它来提取数据，但这给了我每个问题以及每个问题的所有答案。

如何确保我只将答案映射到所属的问题？

SELECT t.testid, 
m.c.value('@ID', 'varchar(max)') as Questionid,
m.c.value('@Prompt', 'varchar(max)') as Questionsprompt,
m.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') as m(c)
CROSS apply t.Questionnaire.nodes('Questions/Question/Answers/Answer') as a(c)

我想要得到的是一个看起来像这样的表：

TestID, QuestionID, CorrectAnswerID, AnswersID, Answervalue
1, 1, 22, 22, "Blue"
1, 1, 22, 23, "green"
1, 1, 22, 24, "pink"
1, 1, 22, 25, "brown"
....
1, 3, 50, 50, "cat"
1, 3, 50, 51, "dog"
1, 3, 50, 52, "bird"