title: SQL XQuery to seperate children/descendants
body: <p>I need to return 2 rows of data from the below XML.</p>
  
  <pre><code>| FaultText                 | Question1             | Question2               | Question3 | SOR    |
  |---------------------------|-----------------------|-------------------------|-----------|--------|
  | Heating: radiator leaking | Q1 = Rad leaking? Yes | Q2 = Where? Bedroom     | Null      | HEA033 |
  | Heating: radiator leaking | Q1 = Rad leaking? Yes | Q2 = Where? Living room | Null      | HEA033 |
  </code></pre>
  
  <p>XML</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;KeyfaxData&gt;
  &lt;Fault name="Fault1" type="RD"&gt;
      &lt;FaultText&gt;Heating: radiator leaking&lt;/FaultText&gt;
      &lt;TenantID&gt;123456&lt;/TenantID&gt;
      &lt;TenantText&gt;Mrs Bean&lt;/TenantText&gt;
      &lt;UserCode&gt;MBEAN&lt;/UserCode&gt;
      &lt;ScriptPath&gt;
          &lt;Question&gt;Q1 = Rad leaking? Yes&lt;/Question&gt;
          &lt;Question&gt;Q2 = Where? Bedroom&lt;/Question&gt;
      &lt;/ScriptPath&gt;
      &lt;Repair name="Repair1"&gt;
          &lt;RepairCodeEx&gt;HEA033&lt;/RepairCodeEx&gt;
          &lt;RepairCodeDesc&gt;HEATING SYSTEM:REPAIR LEAK&lt;/RepairCodeDesc&gt;
      &lt;/Repair&gt;
  &lt;/Fault&gt;
  &lt;Fault name="Fault2" type="RD"&gt;
      &lt;FaultText&gt;Heating: radiator leaking&lt;/FaultText&gt;
      &lt;TenantID&gt;123457&lt;/TenantID&gt;
      &lt;TenantText&gt;Mrs Bean&lt;/TenantText&gt;
      &lt;UserCode&gt;MBEAN1&lt;/UserCode&gt;
      &lt;ScriptPath&gt;
          &lt;Question&gt;Q1 = Rad leaking? Yes&lt;/Question&gt;
          &lt;Question&gt;Q2 = Where? Living room&lt;/Question&gt;
      &lt;/ScriptPath&gt;
      &lt;Repair name="Repair1"&gt;
          &lt;RepairCodeEx&gt;HEA033&lt;/RepairCodeEx&gt;
          &lt;RepairCodeDesc&gt;HEATING SYSTEM:REPAIR LEAK&lt;/RepairCodeDesc&gt;
      &lt;/Repair&gt;
  &lt;/Fault&gt;
  &lt;GUID&gt;AA9F632B-8F68-4D30-A954B40517C01947&lt;/GUID&gt;
  &lt;Status&gt;1&lt;/Status&gt;
  &lt;/KeyfaxData&gt;
  </code></pre>
  
  <p>SQL used before finding the Fault 'name', is below. The results of this shows Question 1 of Fault 2 inside Question 3.</p>
  
  <pre><code>SELECT
  exportxml.value('(/KeyfaxData/Fault/FaultText)[1]','nvarchar(500)') as FaultText,
  exportxml.value('(/KeyfaxData/Fault/ScriptPath/Question)[1]','nvarchar(500)') as Question1,
  exportxml.value('(/KeyfaxData/Fault/ScriptPath/Question)[2]','nvarchar(500)') as Question2,
  exportxml.value('(/KeyfaxData/Fault/ScriptPath/Question)[3]','nvarchar(500)') as Question3,
  exportxml.value('(/KeyfaxData/Fault/Repair/RepairCode)[1]','nvarchar(500)') as SOR,
  FROM KeyFaxHistory
  </code></pre>
  
  <p>Any help would be massively appreciated! I haven't done any XQuery before.</p>