title: Query XML Node Value by Node Attribute
body: <p>I'm trying to retrieve the value of a xml node that is stored as an xml datatype in a sql server column.  For example my xml column data is:</p>
  
  <pre><code>&lt;fields&gt;
  &lt;field id="StudentName"&gt;John Smith&lt;/field&gt;
  &lt;field id="StudentID"&gt;1310021003&lt;/field&gt;
  &lt;field id="SchoolName"&gt;Little Kid Elementary School&lt;/field&gt;
  &lt;/fields&gt;
  </code></pre>
  
  <p>I want to retrieve the StudentID.  When I run the script below I get null.  </p>
  
  <pre><code>select MyColumnName.value('(/fields/field/@StudentID)[1]', 'varchar(20)') as StudentId from MyTable
  </code></pre>
  
  <p>[bonus question] - I would also like to query the table by studentid if possible, for example: <code>select * from MyTable where MyColumnName.value('(/fields/field/@StudentID)[1]', 'varchar(20)') = '1310021003'</code></p>