title: How to convert XML data with attributes to SQL Row
body: <p>I need to translate XML data to a SQL Table whereby the XML data is contained in a column within an SQL Table. 
  The XML Data contains several attributes and nodes.</p>
  
  <p>The XML data consists of questions with multiple choice answers. Each test has a diffrent number of questions and each question can have a diffrent number of multiple choice options. Each question only has one correct answer.</p>
  
  <p>I have read several sites about how to bring XML Data to SQL tables however I cant seem to find a solution for my specific problem as the XML data is pretty complex (at least for me having not much experience with it)</p>
  
  <p>The Table I work with:</p>
  
  <p>Table Name: Tests</p>
  
  <p>Columns:</p>
  
  <pre><code>TestID - 1
  Name - "Test"
  Description - "This is a test"
  ...
  Questionnaire - Containing the XML data
  </code></pre>
  
  <p>The XML data within the questionnaire column looks like this:</p>
  
  <pre><code>&lt;Questions&gt;
    &lt;Question ID="1" Prompt="What colour is the sky?" CorrectAnswerID="22"&gt;
      &lt;Answers&gt;
        &lt;Answer ID="22" Prompt="blue" /&gt;
        &lt;Answer ID="23" Prompt="green" /&gt;
        &lt;Answer ID="24" Prompt="pink" /&gt;
        &lt;Answer ID="25" Prompt="brown" /&gt;
      &lt;/Answers&gt;
    &lt;/Question&gt;
  </code></pre>
  
  <p>This group of code is repeating for every question within this XML column.</p>
  
  <p>I tried to extract the data in with that, but that gives me every question with all answers of every question. </p>
  
  <p>How do I make sure I only map the answers to the question the belong to? </p>
  
  <pre><code>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)
  </code></pre>
  
  <p>What I want to get is a table that looks like this:</p>
  
  <pre><code>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"
  </code></pre>