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

Question

question

TZacks-2728 asked Oct 28, '21 MelissaMa-msft commented Nov 4, '21

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

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

sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-10-28T08:25:27.413Z

MelissaMa-msft answered Oct 28, '21 MelissaMa-msft commented Nov 4, '21

Hi @TZacks-2728，

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.

· 4

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TZacks-2728 · Oct 29, 2021 at 08:57 AM

Thank you for reply.

please tell me which would be best approach CROSS APPLY or INNER JOIN in terms of performance ?

0 ·

MelissaMa-msft TZacks-2728 · Oct 29, 2021 at 09:09 AM

Hi @TZacks-2728，

You could refer more details in this link.

please tell me which would be best approach CROSS APPLY or INNER JOIN in terms of performance ?

Actually it depends. You could check the actual execution plan while executing both queries and choose the faster one.

In your situation, both of them have similar performance.

CROSS APPLY works better on things that have no simple JOIN condition.

You could replace INNER JOIN with CROSS APPLY in below situations:

Join two tables based on TOP n results
When we need INNER JOIN functionality using functions.

Best regards,
Melissa

1 ·

TZacks-2728 · Nov 04, 2021 at 07:23 AM

Can we join two result set using CROSS APPLY where i will not mention join field name other than xml nodes joining. thanks

0 ·

MelissaMa-msft TZacks-2728 · Nov 04, 2021 at 08:23 AM

Hi @TZacks-2728，

Actually it depends on your requirement and actual data.

Could you please provide one example?

Thanks.

Best regards,
Melissa

0 ·

question