title: How to retrieve multiple values of a Xml node in Sql Server 2008
body: <p>Look this Xml</p>
  
  <pre><code>&lt;Criteres&gt;
     &lt;TypesOffre&gt;
        &lt;TypeOffre&gt;Appartement&lt;/TypeOffre&gt;
        &lt;TypeOffre&gt;Maison&lt;/TypeOffre&gt;
     &lt;/TypesOffre&gt;
  &lt;/Criteres&gt;
  </code></pre>
  
  <p>I would like to retrieve the values of TypeOffre node and use them in a JOIN like the following statement</p>
  
  <pre><code>SELECT T2.Nom
  FROM RechercheOffre T1
       INNER JOIN TypeOffre T2 ON (T1.Xml.value('(/Criteres/TypesOffre/TypeOffre)[1]', 'varchar(max)') = T2.Nom)
  </code></pre>
  
  <p>I can't use the value method because she needs a single instance ...</p>
  
  <p>and the query method returns the values of the Typeoffre node in a single column like this </p>
  
  <pre><code>AppartementMaison
  </code></pre>
  
  <p>Any idea ?</p>