Merge statement and while loop used

Question

please see the below code which is not very clear to me what they are doing. why they are using while loop.

specially these below line not clear

A)
1) CROSS APPLY x.nodes('/Root/Orders[position()>= sql:variable("@START ") and position() <= sql:variable("@Eslam Nader ")]')m(n)
2) CROSS APPLY n.nodes('OrderDetails') p(q)

B)
How below join is working
FROM @t t
CROSS APPLY x.nodes('/Root/Orders[position()>= sql:variable("@START ") and position() <= sql:variable("@Eslam Nader ")]')m(n)
CROSS APPLY n.nodes('OrderDetails') p(q)

C) Why they use CROSS APPLY ? are they using cross apply for join or for any other purpose ?
when to use cross apply. please guide me a scenario when people should use cross apply?

declare @t table  
(  
x xml   
)  
insert @t  
values  
('<Root>  
  <Orders>  
    <OrderID>10248</OrderID>  
    <CustomerID>VINET</CustomerID>  
    <EmployeeID>5</EmployeeID>  
    <OrderDate>1996-07-04T00:00:00</OrderDate>  
    <RequiredDate>1996-08-01T00:00:00</RequiredDate>  
    <ShippedDate>1996-07-16T00:00:00</ShippedDate>  
    <ShipVia>3</ShipVia>  
    <Freight>32.3800</Freight>  
    <ShipName>Vins et alcools Chevalier</ShipName>  
    <ShipAddress>59 rue de l''Abbaye</ShipAddress>  
    <ShipCity>Reims</ShipCity>  
    <ShipPostalCode>51100</ShipPostalCode>  
    <ShipCountry>France</ShipCountry>  
    <OrderDetails>  
      <OrderID>10248</OrderID>  
      <ProductID>11</ProductID>  
      <UnitPrice>14.0000</UnitPrice>  
      <Quantity>12</Quantity>  
      <Discount>0.0000000e+000</Discount>  
    </OrderDetails>  
    <OrderDetails>  
      <OrderID>10248</OrderID>  
      <ProductID>42</ProductID>  
      <UnitPrice>9.8000</UnitPrice>  
      <Quantity>10</Quantity>  
      <Discount>0.0000000e+000</Discount>  
    </OrderDetails>  
    <OrderDetails>  
      <OrderID>10248</OrderID>  
      <ProductID>72</ProductID>  
      <UnitPrice>34.8000</UnitPrice>  
      <Quantity>5</Quantity>  
      <Discount>0.0000000e+000</Discount>  
    </OrderDetails>  
  </Orders>  
  <Orders>  
    <OrderID>10249</OrderID>  
    <CustomerID>TOMSP</CustomerID>  
    <EmployeeID>6</EmployeeID>  
    <OrderDate>1996-07-05T00:00:00</OrderDate>  
    <RequiredDate>1996-08-16T00:00:00</RequiredDate>  
    <ShippedDate>1996-07-10T00:00:00</ShippedDate>  
    <ShipVia>1</ShipVia>  
    <Freight>11.6100</Freight>  
    <ShipName>Toms Spezialitäten</ShipName>  
    <ShipAddress>Luisenstr. 48</ShipAddress>  
    <ShipCity>Münster</ShipCity>  
    <ShipPostalCode>44087</ShipPostalCode>  
    <ShipCountry>Germany</ShipCountry>  
    <OrderDetails>  
      <OrderID>10249</OrderID>  
      <ProductID>14</ProductID>  
      <UnitPrice>18.6000</UnitPrice>  
      <Quantity>9</Quantity>  
      <Discount>0.0000000e+000</Discount>  
    </OrderDetails>  
    <OrderDetails>  
      <OrderID>10249</OrderID>  
      <ProductID>51</ProductID>  
      <UnitPrice>42.4000</UnitPrice>  
      <Quantity>40</Quantity>  
      <Discount>0.0000000e+000</Discount>  
    </OrderDetails>  
  </Orders>  
  <Orders>  
    <OrderID>10250</OrderID>  
    <CustomerID>HANAR</CustomerID>  
    <EmployeeID>4</EmployeeID>  
    <OrderDate>1996-07-08T00:00:00</OrderDate>  
    <RequiredDate>1996-08-05T00:00:00</RequiredDate>  
    <ShippedDate>1996-07-12T00:00:00</ShippedDate>  
    <ShipVia>2</ShipVia>  
    <Freight>65.8300</Freight>  
    <ShipName>Hanari Carnes</ShipName>  
    <ShipAddress>Rua do Paço, 67</ShipAddress>  
    <ShipCity>Rio de Janeiro</ShipCity>  
    <ShipRegion>RJ</ShipRegion>  
    <ShipPostalCode>05454-876</ShipPostalCode>  
    <ShipCountry>Brazil</ShipCountry>  
    <OrderDetails>  
      <OrderID>10250</OrderID>  
      <ProductID>41</ProductID>  
      <UnitPrice>7.7000</UnitPrice>  
      <Quantity>10</Quantity>  
      <Discount>0.0000000e+000</Discount>  
    </OrderDetails>  
    <OrderDetails>  
      <OrderID>10250</OrderID>  
      <ProductID>51</ProductID>  
      <UnitPrice>42.4000</UnitPrice>  
      <Quantity>35</Quantity>  
      <Discount>1.5000001e-001</Discount>  
    </OrderDetails>  
    <OrderDetails>  
      <OrderID>10250</OrderID>  
      <ProductID>65</ProductID>  
      <UnitPrice>16.8000</UnitPrice>  
      <Quantity>15</Quantity>  
      <Discount>1.5000001e-001</Discount>  
    </OrderDetails>  
  </Orders>  
</Root>')  
  
  
declare @i int = 1  
declare @start int,@end int  
  
WHILE @i <= 10  
BEGIN  
  
SELECT @start = (@i-1) * 10 + 1,  
@end = @i * 10  
  
MERGE YourTable AS t  
USING  
(  
  
SELECT n.value('OrderID[1]','int') AS OrderID,  
    n.value('CustomerID[1]','varchar(100)') AS CustomerID,  
    n.value('EmployeeID[1]','int') AS EmployeeID,  
    n.value('OrderDate[1]','datetime') AS OrderDate,  
    n.value('RequiredDate[1]','datetime') AS RequiredDate,  
    n.value('ShippedDate[1]','datetime') AS ShippedDate,  
    n.value('ShipVia[1]','int') AS ShipVia,  
    n.value('Freight[1]','decimal(15,4)') AS Freight,  
    n.value('ShipName[1]','varchar(100)') AS ShipName,  
    n.value('ShipAddress[1]','varchar(1000)') AS ShipAddress,  
    n.value('ShipCity[1]','varchar(100)') AS  ShipCity,  
    n.value('ShipPostalCode[1]','varchar(100)') AS ShipPostalCode,  
    n.value('ShipCountry[1]','varchar(100)') AS ShipCountry,  
	q.value('ProductID[1]','int') AS ProductID,  
	q.value('UnitPrice[1]','decimal(15,4)') AS UnitPrice,  
	q.value('Quantity[1]','int') AS Quantity,  
	q.value('Discount[1]','float') AS Discount  
FROM @t t  
CROSS APPLY x.nodes('/Root/Orders[position()>= sql:variable("@start") and position() <= sql:variable("@end")]')m(n)  
CROSS APPLY n.nodes('OrderDetails') p(q)  
) AS s  
ON s.OrderID = t.OrderID  
WHEN MATCHED   
THEN UPDATE t SET CustomerID = s.CustomerID,EmployeeID = s.EmployeeID,...  
WHEN NOT MATCHED BY TARGET  
THEN INSERT(CustomerID,EmployeeID,..) VALUES (s.CustomerID,s.EmployeeID,..)  
WHEN NOT MATCHED BY SOURCE  
THEN DELETE  
END;  
  
SET @i = @i + 1  
  
END  

Answer 1

Hi @Sudip Bhatt ,

As mentioned in forum link provided by Viorel, SQL is always optimized for set based operations so unless you've really huge table and xml document there's no need to do this in batches. Or it is requested only 10 records at a time.

But in your example, there is no need to use loop since there are only 8 records totally.

CROSS APPLY x.nodes('/Root/Orders[position()>= sql:variable("@START ") and position() <= sql:variable("@Eslam Nader ")]')m(n)

Per my understanding, this part is to get the nodes under /Root/Orders at the specified positions. In your example, the positions are only from 1 to 3.

CROSS APPLY n.nodes('OrderDetails') p(q)

CROSS APPLY is used because there are some sub nodes under /Root/Orders/OrderDetails. We need to apply all of them with related OrderIDs.

Why they use CROSS APPLY ? are they using cross apply for join or for any other purpose ?
when to use cross apply. please guide me a scenario when people should use cross apply?

Please refer below one simple example:

DECLARE @XML XML  
SET @XML =  
'<Input>  
    <Courses>  
        <Course>  
            <Id>27</Id>  
            <Students>  
                <Id>19876</Id>  
                <Id>19878</Id>  
            </Students>  
        </Course>  
        <Course>  
            <Id>29</Id>  
            <Students>  
                <Id>19879</Id>  
            </Students>  
        </Course>  
    </Courses>  
</Input>'  

-- Method# 1. Simple approach but bit costly | Query Cost: 60%  
select t.c.value('../../Id[1]', 'INT') as CourseId,  
    t.c.value('.', 'INT') as Students  
from @XML.nodes('//Input/Courses/Course/Students/Id') as t(c)  
   
-- Method# 2. By using Cross Apply | Query Cost: 40%  
select t.c.value('Id[1]', 'INT') as CourseId,  
    t1.c1.value('.', 'INT') as Students  
from @XML.nodes('//Input/Courses/Course') as t(c)  
cross apply t.c.nodes('Students/Id') as t1(c1)  

The above 2 approaches shows that the second one with CROSS APPLY is much more performant.

– The traditional approach (#1) traverses the nodes (parent/child) and pulls the desired data.
– But the 2nd one with APPLY clause fetches specific node’s entire row and join it with the SELECTed data.

You could refer more details in below links:
Why is CROSS APPLY needed when using XPath queries?
more examples on querying XML …with CROSS APPLY & XQuery – in SQL Server

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
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.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet