Hi @T.Zacks ,
In this situation, we have to extract values from two xmls and treat them as two tables , and perform the cross apply or join using OrderID column.
Please refer to below:
SELECT a.OrderID,c.ProductID,c.Price,c.Qty FROM
(SELECT OrderID = O.n.value('(OrderID/text())[1]', 'int'),
CustomerID = O.n.value('(CustomerID/text())[1]', 'nchar(5)'),
OrderDate = O.n.value('(OrderDate/text())[1]', 'datetime'),
EmployeeId = O.n.value('(EmployeeID/text())[1]', 'smallint')
FROM @x1.nodes('/Orders/Order') AS O(n)) a
CROSS APPLY
(
SELECT * FROM (
SELECT OrderID = D.n.value('(OrderID/text())[1]', 'int'),
ProductID = D.n.value('(ProductID/text())[1]', 'int'),
Price = D.n.value('(Price/text())[1]', 'decimal(10,2)'),
Qty = D.n.value('(Qty/text())[1]', 'int')
FROM @x2.nodes('/Orders/OrderDetails') AS D(n)) b
WHERE a.OrderID=b.OrderID
) c
OR
select a.OrderID,b.ProductID,b.Price,b.Qty from (
SELECT OrderID = O.n.value('(OrderID/text())[1]', 'int'),
CustomerID = O.n.value('(CustomerID/text())[1]', 'nchar(5)'),
OrderDate = O.n.value('(OrderDate/text())[1]', 'datetime'),
EmployeeId = O.n.value('(EmployeeID/text())[1]', 'smallint')
FROM @x1.nodes('/Orders/Order') AS O(n))a
inner join (
SELECT OrderID = D.n.value('(OrderID/text())[1]', 'int'),
ProductID = D.n.value('(ProductID/text())[1]', 'int'),
Price = D.n.value('(Price/text())[1]', 'decimal(10,2)'),
Qty = D.n.value('(Qty/text())[1]', 'int')
FROM @x2.nodes('/Orders/OrderDetails') AS D(n)) b
on a.OrderID=b.OrderID
Output:
OrderID ProductID Price Qty
13000 76 123.00 10
13000 16 3.23 20
13001 12 12.23 1
Best regards,
Melissa
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.