title: SQL 2008: getting rows with a join to an XML field?
body: <p>Not sure if this question makes for some poor performance down the track, but seems to at least feel "a better way" right now..</p>
  
  <p>What I am trying to do is this:</p>
  
  <p>I have a table called CONTACTS which amongst other things has a primary key field called memberID</p>
  
  <p>I also have an XML field which contains the ID's of your friends (for example).. like:</p>
  
  <pre><code>  &lt;root&gt;&lt;id&gt;2&lt;/id&gt;&lt;id&gt;6&lt;/id&gt;&lt;id&gt;14&lt;/id&gt;&lt;/root&gt;
  </code></pre>
  
  <p>So what I am trying to do via a stored proc is pass in say your member ID, and return all of your friends info, for example:</p>
  
  <pre><code>  select name, address, age, dob from contacts
    where id... xml join stuff...
  </code></pre>
  
  <p>The previous way I had it working (well sort of!) selected all the XML nodes (/root/id) into a temp table, and then did a join from that temp table to the contact table to get the contact fields...</p>
  
  <p>Any help much appreciated.. just a bit overloaded from the .query .nodes examples, and of course which is maybe a better way of doing this...</p>
  
  <p>THANKS IN ADVANCE!</p>
  
  <p>&lt;-- EDIT -->
  I did get something working, but looks like a SQL frankenstein statement!
  Basically I needed to get the friends contact ID's from the XML field, and populate into a temp table like so:</p>
  
  <pre><code>Declare @contactIDtable TABLE (ID int)
  INSERT INTO @contactIDtable (ID)
          SELECT CONVERT(INT,CAST(T2.memID.query('.') AS varchar(100))) AS friendsID
          FROM dbo.members
          CROSS APPLY memberContacts.nodes('/root/id/text()') AS T2(memID)
  </code></pre>
  
  <p>But crikey! the convert/cast thing looks serious.. as I need to get an INT for the next bit which is the actual join to return the contact data as follows:</p>
  
  <pre><code>SELECT memberID, memberName, memberAddress1
      FROM members
      INNER JOIN @contactIDtable cid
      ON members.memberID = cid.ID
      ORDER BY memberName
  </code></pre>
  
  <p>RESULT...
  Well it works.. in my case, my memberContacts XML field had 3 nodes (id's in this case), and the above query returned 3 rows of data (memberID, memberName, memberAddress1)... </p>
  
  <p>The whole point of this of course was to try to save creating a many join table i.e. list of all my friends ID's... just not sure if the above actually makes this quicker and easier...</p>
  
  <p>Anymore ideas / more efficient ways of trying to do this???</p>