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

Question

Hi there, 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. list 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???

Answer 1

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.

Answer 2

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