title: iterate on JOIN
body: <p>I have two tables where one is a container and the other are items. 
  I want to find an item then find all containers that contain that specific item.
  I have the query but it will find only one container. It's all XML which makes it a little complicated.</p>
  
  <p>(I search in table1 inside xml then when I find what I want I get the guid from the col and then search in table12 xml using the guid I found in table1 )</p>
  
  <p>table1</p>
  
  <pre><code>Guid |    Name   |   t1XML
   1          n1       file1
   2          n2       file2
  </code></pre>
  
  <p>table2</p>
  
  <pre><code>Guid   |    Name   |   t2XML
    7          n7        cont7
    9          n9        cont9
  </code></pre>
  
  <p>** guids are normal guids (36 char)</p>
  
  <p>this is what I have</p>
  
  <pre><code>DECLARE @searchFor NVARCHAR(max)=N'context' 
  SELECT t1.Name as itemName, t1.Guid, t2.Name
  FROM table1 AS t1 
  JOIN table2 AS t2 ON t2.t2XML.query('/t/n').value('.', 'VARCHAR(36)')= CAST(t1.Guid as varchar(36))
  WHERE t1.t1XML.exist(N'/t/am[text()[1]=sql:variable("@searchFor")]')=1 order by t2.Name
  </code></pre>
  
  <p>so what I want is, when I find an item from t1 which has 'context', I want the join to iterate to t2 and find all containers that has this item.  </p>