SQL Server: How to join two xml data using cross apply

T.Zacks 3,996 Reputation points
2021-10-28T08:14:14.063+00:00

When two related entities wrapped in same xml then below code will work but when two related entities stored in two different xml variable then how could i join using CROSS APPLY?

please share idea.

DECLARE @x xml = 
'<Orders>
    <Order>
       <OrderID>13000</OrderID>
       <CustomerID>ALFKI</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>2</EmployeeID>
       <OrderDetails>
           <ProductID>76</ProductID>
           <Price>123</Price>
           <Qty>10</Qty>
       </OrderDetails>
       <OrderDetails>
           <ProductID>16</ProductID>
           <Price>3.23</Price>
           <Qty>20</Qty>
       </OrderDetails>
    </Order>
    <Order>
       <OrderID>13001</OrderID>
       <CustomerID>VINET</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>1</EmployeeID>
       <OrderDetails>
           <ProductID>12</ProductID>
           <Price>12.23</Price>
           <Qty>1</Qty>
       </OrderDetails>
    </Order>
</Orders>'

SELECT OrderID    = T.Item.value('(OrderID/text())[1]', 'int'),
       CustomerID = T.Item.value('(CustomerID/text())[1]', 'nchar(5)'),
       OrderDate  = T.Item.value('(OrderDate/text())[1]',  'datetime'),
       EmployeeId = T.Item.value('(EmployeeID/text())[1]', 'smallint')
FROM   @x.nodes('Orders/Order') AS T(Item)
SELECT OrderID    = O.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   @x.nodes('/Orders/Order') AS O(n)
CROSS APPLY O.n.nodes('OrderDetails') AS D(n)

the above code is working as expected.

A sample data


DECLARE @x1 xml = 
'<Orders>
    <Order>
       <OrderID>13000</OrderID>
       <CustomerID>ALFKI</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>2</EmployeeID>
    </Order>
    <Order>
       <OrderID>13001</OrderID>
       <CustomerID>VINET</CustomerID>
       <OrderDate>2006-09-20Z</OrderDate>
       <EmployeeID>1</EmployeeID>
    </Order>
</Orders>'

DECLARE @x2 xml = 
'<Orders>
      <OrderDetails>
           <OrderID>13000</OrderID>
           <ProductID>76</ProductID>
           <Price>123</Price>
           <Qty>10</Qty>
       </OrderDetails>
       <OrderDetails>
           <OrderID>13000</OrderID>
           <ProductID>16</ProductID>
           <Price>3.23</Price>
           <Qty>20</Qty>
       </OrderDetails>
       <OrderDetails>
           <OrderID>13001</OrderID>
           <ProductID>12</ProductID>
           <Price>12.23</Price>
           <Qty>1</Qty>
       </OrderDetails>
</Orders>'

please tell me how could i join two xml stored in two different xml type variable @x1 & @x2 using cross apply

Thanks

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,689 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,211 Reputation points
    2021-10-28T08:25:27.413+00:00

    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.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.