title: Read child node along with its parent attribute from XML in SQL server
body: <p>I have an XML similar to this structure.</p>
  
  <pre><code>&lt;Root&gt;
  &lt;id&gt;a2bh5&lt;/id&gt;
  &lt;Student ID="123"&gt;
      &lt;Name&gt;abc&lt;Name&gt;
      &lt;course&gt;ETL&lt;course&gt;
      &lt;Scores&gt;
          &lt;Score&gt;
              &lt;Subject&gt;SSIS&lt;subject&gt;
              &lt;grade&gt;B&lt;grade&gt;
          &lt;/score&gt;
          &lt;Score&gt;
              &lt;Subject&gt;Informatica&lt;subject&gt;
              &lt;grade&gt;C&lt;grade&gt;
          &lt;/score&gt;
      &lt;/Scores&gt;
  &lt;/Student&gt;
  &lt;Student ID="456"&gt;
      &lt;Name&gt;xyz&lt;Name&gt;
      &lt;course&gt;ETL&lt;course&gt;
      &lt;Scores&gt;
          &lt;Score&gt;
              &lt;Subject&gt;Pentaho&lt;subject&gt;
              &lt;grade&gt;F&lt;grade&gt;
          &lt;/score&gt;
          &lt;Score&gt;
              &lt;Subject&gt;Datastage&lt;subject&gt;
              &lt;grade&gt;A&lt;grade&gt;
          &lt;/score&gt;
      &lt;/Scores&gt;
  &lt;/Student&gt;
  &lt;/Root&gt;
  </code></pre>
  
  <p>I would like to get details from child nodes(scores) along with its parent attribute(Id) using Xquery in SQL server. </p>
  
  <p>The query result is expected like below for all subjects. Please help.</p>
  
  <pre><code>Student_Id subject grade
  ========================
  123     SSIS        B   
  </code></pre>