@sriram-2 and @stewart, Thanks for the solution, both works perfectly.

@sriram-2, I am actually populating a table variable through this query, hence wanted to get the join working.

my xml dataset is...