SQL Server - XML Column - Use value to join? - Asked By c g on 14-Jul-06 04:40 AM

Hi,
I have an XML column which has an xml doc.
Can I use a value in my xml doc to join with another column in a different table?

Xml Column Join - Asked By Ramya T on 14-Jul-06 01:31 PM

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]

Xml Column - Asked By c g on 14-Jul-06 02:06 PM

Thanks for the reply.
So I have to create a temporary table and use that to join....

Using IN - Asked By Ramya T on 14-Jul-06 03:26 PM

Easiest without creating temp table is using IN operator 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>'  
SELECT T.* 
FROM dbo.YourTableName T
WHERE T.Name IN 
(
SELECT Team.player.value(         './@name', 'nvarchar(10)') AS Name FROM @doc.nodes('/Team/Players/Pitcher')      Team(player)
)
[/CODE]