SQL Server: XML node join & Cross Apply

Question

In the below xml cross apply used to join two node. when we join in SQL we mention column name of two table but in cross apply we do not mention column name. so on which column join is performing ?

How sql server understand on which column join will be performing ?

     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    = O.n.value('(OrderID/text())[1]',   'int'),
            CustomerID = O.n.value('(CustomerID/text())[1]',   'VARCHAR(MAX)'),
            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)

Here i am giving small example of cross apply where
SELECT * FROM Department D
CROSS APPLY
(
SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A

see above cross apply here i am mentioning column name on which join will be performing but when join two xml node using cross apply then no column name not mention from two node.....but sql is working....how ?

please help me to understand the situation. thanks

Answer 1

Hi @T.Zacks ,

• XQuery .nodes() method creates a rectangular dataset.
• CROSS APPLY O.n.nodes(... creates a second rectangular dataset for each row in the 1st dataset. By doing that it simulates a relationship.

Access to the datasets columns is via the .value() method.

P.S. Please contact me on LinkedIn.

Answer 2

Hi @T.Zacks ,

Per my understanding, in your small table example,Department and Employee tables are two independent tables. The only relationship is that both of them have the same column DepartmentID. So we have to add the E.DepartmentID = D.DepartmentID to make the results in order.

Howerver, in your xml example, the first one is @x.nodes('/Orders/Order') AS O(n) which could output as a table with values of OrderID and CustomerID. The second one O.n.nodes('OrderDetails') is based on the first table instead of another independent table.

So CROSS APPLY applies nodes() to each row based the first table and returns only the related rows that produce a result set. So there is no need to add extra column to join each other.

Hope above could be a little helpful to you.

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.