Tsql XML join with table

TableProduct(ProductID, ProductName)
---------------------------------------------------------
21                      Product21
22                      Product22

TableProductXML(ProductID, XMLDATA)
------------------------
21,
<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>

TableWarehouse(WH_ID, Location)
-------------------------
1                          NJ
2                          NY
3                          CT

Looking for TSQL result as below

ProductId      ProductName      WH_Id      WH_Name      WHL_Id      WH_Location
21                     Product 21                     1      Name1                     3                    CT
21                     Product 21                     2      Name2                     2                     NY
21                     Product 21                     3      Name3                     1                     NJ
Bharat GuruAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can have something like this:

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

Open in new window

0
Bharat GuruAuthor Commented:
From same TableProductXML table how can I return below result

ProductID         WH_ID   Name     WHL_ID    
21                          1                 Name1     3
21                         2                 Name2     2
21                         3                 Name3     1
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try this instead. you can slightly amend what I have posted:

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,
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
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bharat GuruAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
T-SQL

From novice to tech pro — start learning today.