One way I can think of is to extract value from Xml column and insert into temp table all the values then join this temp table with your different table like this.
[CODE]
DECLARE @doc xml
SELECT @doc = '
<Team name="Braves">
<Players>
<Pitcher name="John" role="Closer">
With team since 1989
</Pitcher>
<Pitcher name="Scott" role="Closer">
With team since 1989
</Pitcher>
</Players>
</Team>'
CREATE TABLE #Name1 (
Name int
)
INSERT INTO #Name1
SELECT Team.player.value(
'./@name', 'nvarchar(10)') as Name
FROM @doc.nodes('/Team/Players/Pitcher')
Team(player)
SELECT T.SpeciesName
FROM #Name1 T1
INNER JOIN dbo.YourTable T
ON T.Name = T1.Name
[/CODE]