SQL 2008: getting rows with a join to an XML field?

Not sure if this question makes for some poor performance down the track, but seems to at least feel "a better way" right now..

What I am trying to do is this:

I have a table called CONTACTS which amongst other things has a primary key field called memberID

I also have an XML field which contains the ID's of your friends (for example).. like:

  <root><id>2</id><id>6</id><id>14</id></root>

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:

  select name, address, age, dob from contacts
  where id... xml join stuff...

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...

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...

THANKS IN ADVANCE!

<-- 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:

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)

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:

SELECT memberID, memberName, memberAddress1
    FROM members
    INNER JOIN @contactIDtable cid
    ON members.memberID = cid.ID
    ORDER BY memberName

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)...

The whole point of this of course was to try to save creating a many join table i.e. l开发者_Go百科ist of all my friends ID's... just not sure if the above actually makes this quicker and easier...

Anymore ideas / more efficient ways of trying to do this???

SQL Server's syntax for reading XML is one of the least intuitive around. Ideally, you'd want to:

select   f.name
from     friends f
join     @xml x
on       x.id = f.id

Instead, SQL Server requires you to spell out everything. To turn an XML variable or column into a "rowset", you have to spell out the exact path and think up two aliases:

@xml.nodes('/root/id') as table_alias(column_alias)

Now you have to explain to SQL Server how to turn <id>1</id> into an int:

table_alias.column_alias.value('.', 'int')

So you can see why most people prefer to decode XML on the client side :)

A full example:

declare @friends table (id int, name varchar(50))
insert @friends (id, name)
          select  2, 'Locke Lamorra'
union all select  6, 'Calo Sanzo'
union all select 10, 'Galdo Sanzo'
union all select 14, 'Jean Tannen'

declare @xml xml
set @xml = ' <root><id>2</id><id>6</id><id>14</id></root>'

select  f.name
from    @xml.nodes('/root/id') as table_alias(column_alias)
join    @friends f
on      table_alias.column_alias.value('.', 'int') = f.id

In order to get your XML contents as rows from a "pseudo-table", you need to use the .nodes() on the XML column - something like:

DECLARE @xmlfield XML
SET @xmlfield = '<root><id>2</id><id>6</id><id>14</id></root>'

SELECT
   ROOT.ID.value('(.)[1]', 'int')
FROM
   @xmfield.nodes('/root/id') AS ROOT(ID)

SELECT
    (list of fields)
FROM
    dbo.Contacts c
INNER JOIN
    @xmlfield.nodes('/root/id') AS ROOT(ID) ON c.ID = Root.ID.value('(.)[1]', 'INT')  

Basically, the .nodes() defines a pseudo-table ROOT with a single column ID, that will contain one row for each node in the XPath expression, and the .value() selects a specific value out of that XML fragment.