title: Using TSQL, XQuery results return > 1 IDs which I need to search within a query
body: <p>I have a column in an SQL Server 2005 DB which contains an XML stored as a string. Within that XML is the following element tree</p>
  
  <pre><code>&lt;DriverDetails&gt;
    &lt;DriverDetail&gt;
      &lt;ID&gt;2334&lt;/ID&gt;
      &lt;PRN&gt;1&lt;/PRN&gt;
    &lt;/DriverDetail&gt;
    &lt;DriverDetail&gt;
      &lt;ID&gt;2335&lt;/ID&gt;
      &lt;PRN&gt;2&lt;/PRN&gt;
    &lt;/DriverDetail&gt;
    &lt;DriverDetail&gt;
      &lt;ID&gt;2336&lt;/ID&gt;
      &lt;PRN&gt;3&lt;/PRN&gt;
    &lt;/DriverDetail&gt;
    &lt;DriverDetail&gt;
      &lt;ID&gt;2337&lt;/ID&gt;
      &lt;PRN&gt;4&lt;/PRN&gt;
    &lt;/DriverDetail&gt;
  &lt;/DriverDetails&gt;
  </code></pre>
  
  <p>I've retrieved this using:</p>
  
  <pre><code>CONVERT(xml, detailRiskInformation).query('
              //DriverDetails
          ')
  </code></pre>
  
  <p>I need to query each ID within each DriverDetail node to see it if exists in another table, called DriverDetails. The relevant column is [DriverDetail].[Id].</p>
  
  <p>Now, I can run XQuery like this:</p>
  
  <pre><code>CONVERT(xml, detailRiskInformation).query('
              for $i in //DriverDetail
              return data( $i )
          ')
  </code></pre>
  
  <p>However it just returns a single result with the 4 IDs separated by spaces.</p>
  
  <p>How can I perform an iterative query on each of these IDs in one query? Or, if not, how can I get these out using a cursor or something much cleverer?</p>
  
  <p>Thanks in advance
  Ant</p>