question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked MelissaMa-msft answered

SQL Server: XML node join & Cross Apply

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

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

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @TZacks-2728,

  • 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.

5 |1600 characters needed characters left characters exceeded

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

MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered

Hi @TZacks-2728,

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.

5 |1600 characters needed characters left characters exceeded

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