select c.Company_ID,

(select r.value('.','int') as ID,

m.Member_name as Name

from c.Member_IDs.nodes('/IDs/ID') as x(r)

inner join Member_info m on m.Member_ID=r.value('.','int')

for xml path(''),root('IDs')) as Member_ID_name

from Company c

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537