Tsql XML join with table

IF OBJECT_ID('tempdb..#TableProduct') IS NOT NULL
	DROP TABLE #TableProduct

;with cte as
(
	select 21 ProductID, 'Product21' ProductName union
	select 22 ProductID, 'Product22' ProductName
)
select * into #TableProduct from cte


IF OBJECT_ID('tempdb..#TableProductXML') IS NOT NULL
	DROP TABLE #TableProductXML

;with cte as
(
	select 21 ProductID, 
	CAST('<DataModel xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ProductId>21</ProductId>
  <PType1>
    <WH>
      <WH_ID>1</WH_ID>
      <Name>Name1</Name>
      <WHL_ID>3</WHL_ID>
    </WH>
    <WH>
      <WH_ID>2</WH_ID>
      <Name>Name2</Name>
      <WHL_ID>2</WHL_ID>
    </WH>
    <WH>
      <WH_ID>3</WH_ID>
      <Name>Name3</Name>
      <WHL_ID>1</WHL_ID>
    </WH>
  </PType1>
</DataModel>' as XML) XMLDATA
)
select * into #TableProductXML from cte

IF OBJECT_ID('tempdb..#TableWarehouse') IS NOT NULL
	DROP TABLE #TableWarehouse

;with cte as
(
	select 1 WH_ID, 'NJ' Location union
	select 2 WH_ID, 'NY' Location union
	select 3 WH_ID, 'CT' Location
)
select * into #TableWarehouse from cte

declare @xml XML, @ProductID int

set @ProductID = 21
select @xml = XMLDATA from #TableProductXML where ProductID = @ProductID


Select @ProductID ProductID,
b.ProductName,
T.C.value('(WH_ID)[1]', 'int') WH_ID,
T.C.value('(Name)[1]', 'varchar(100)') Name,
T.C.value('(WHL_ID)[1]', 'int') WHL_ID,
c.Location WH_Location
FROM @xml.nodes('//WH') T(C)
left join #TableProduct b on @ProductID = b.ProductID
left join #TableWarehouse c on T.C.value('(WH_ID)[1]', 'int') = c.WH_ID

Select allOpen in new window