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]