title: How do I get the child node values AND parent node values in SQL XPath
body: <p>My data (passed into a parameter (@Data XML) in a Stored Procedure) looks like this:</p>
  
  <pre><code>&lt;Records&gt;
      &lt;Record id="1"&gt;
          &lt;Data&gt;
              &lt;FirstName&gt;John&lt;/FirstName&gt;
              &lt;LastName&gt;Doe&lt;/LastName&gt;
          &lt;/Data&gt;
          &lt;Result&gt;
              &lt;StatusId&gt;3&lt;/StatusId&gt;
              &lt;ErrorCodes&gt;
                  &lt;Item&gt;4&lt;/Item&gt;
                  &lt;Item&gt;23&lt;/Item&gt;
                  &lt;Item&gt;19&lt;/Item&gt;
              &lt;/ErrorCodes&gt;
          &lt;/Result&gt;
      &lt;/Record&gt;
     &lt;Record id="2"&gt;
          &lt;Data&gt;
              &lt;FirstName&gt;Fred&lt;/FirstName&gt;
              &lt;LastName&gt;Blog&lt;/LastName&gt;
          &lt;/Data&gt;
          &lt;Result&gt;
              &lt;StatusId&gt;2&lt;/StatusId&gt;
              &lt;ErrorCodes&gt;
                  &lt;Item&gt;1&lt;/Item&gt;
                  &lt;Item&gt;3&lt;/Item&gt;
              &lt;/ErrorCodes&gt;
          &lt;/Result&gt;
      &lt;/Record&gt;
  &lt;/Records&gt;
  </code></pre>
  
  <p>I want to select the Record id and the Error Codes, like this:</p>
  
  <pre><code>id    Item
  ----------
   1       4
   1      23
   1      19
   2       1
   2       3
  </code></pre>
  
  <p>The order of data doesn't matter.</p>
  
  <p>The following gets me the Error Codes, but not the Record id:</p>
  
  <pre><code>SELECT Data.value('.', 'int') as ErrorCode
  FROM @Data.nodes('/Records/Record/Result/ErrorCodes/*') AS data(Data)
  </code></pre>