title: Select records from XML string in SQL server
body: <p>I have the below XML and want to select all the <code>allergenID</code> records</p>
  
  <pre><code> DECLARE @Request XML
  
   SET @Request = '
  &lt;ProductAllergen&gt;
  &lt;!---Gluten (Barley)--&gt;
  &lt;Allergen&gt;
    &lt;AllergenID&gt;1&lt;/AllergenID&gt;
    &lt;AllergenStatusID&gt;1&lt;/AllergenStatusID&gt;
  &lt;/Allergen&gt;
  &lt;!---Gluten (Wheat)--&gt;
  &lt;Allergen&gt;
    &lt;AllergenID&gt;2&lt;/AllergenID&gt;
    &lt;AllergenStatusID&gt;1&lt;/AllergenStatusID&gt;
  &lt;/Allergen&gt;
  &lt;!---Gluten (Rye)--&gt;
  &lt;Allergen&gt;
    &lt;AllergenID&gt;3&lt;/AllergenID&gt;
    &lt;AllergenStatusID&gt;1&lt;/AllergenStatusID&gt;
  &lt;/Allergen&gt;
  &lt;!---Eggs--&gt;
  &lt;Allergen&gt;
    &lt;AllergenID&gt;4&lt;/AllergenID&gt;
    &lt;AllergenStatusID&gt;1&lt;/AllergenStatusID&gt;
  &lt;/Allergen&gt;
   &lt;/ProductAllergen&gt;
  </code></pre>
  
  <p>Currently this code gives me the first allergen record. (taking out the [1] didn't work)</p>
  
  <pre><code>SELECT  @Request.value('(//AllergenStatusID)[1]','int')
  </code></pre>