How to join regular table and xquery resultset

Question

suppose this is my xquery

SELECT d.v.value('(Period/text())[1]','VARCHAR(MAX)') AS Period
FROM @DistinctPeriods.nodes('/Root/NewPeriodData') AS d(v)

Now i want to join period with another table say table1 which has period column.

how to perform this join ?

https://stackoverflow.com/a/8495566/13722367

Answer 1

Hi @Sudip Bhatt ,

Please have a try with below:

;with cte as (  
SELECT d.v.value('(Period/text())[1]','VARCHAR(MAX)') AS Period  
FROM @DistinctPeriods.nodes('/Root/NewPeriodData') AS d(v))  
  
select * from cte a   
inner join table1  b on a.Period=b.Period  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Since you are only telling half the story, you leave us guessing. But maybe this is what you are looking for:

SELECT *  
FROM   table1  
WHERE  EXISTS (SELECT *  
               FROM   @DistinctPeriods.nodes('/Root/NewPeriodData') AS d(v)  
               WHERE  d.v.value('(Period/text())[1]','int') = table1.period)   

Note that I have changed the data type. I don't what data is in Period, but you would normally not join a varchar(MAX) value.