SQL XML - CROSS APPLY V/S OUTER APPLY

Hello Friends,

I was working on an XML data in SQL and I was confused when using CROSS APPLY OR OUTER APPLY.

So I tried to find the difference and felt this is worth to share.

Please Take a look forward and find the difference between CROSS APPLY and OUTER APPLY

The APPLY operator comes in two variants, CROSS APPLY and OUTER APPLY. 

CROSS APPLY is equivalent to an INNER JOIN expression and 

OUTER APPLY is equivalent to a LEFT OUTER JOIN expression.

Sample XML

DECLARE @Xml AS XML =

 '<Child1 Data="Child1-Data">
          <Child2 Data="Child2-Data-1">
                <Child3 Data="Child3-Data" />
         </Child2>
         <Child2 Data="Child2-Data-2">
        </Child2>
</Child1>'

Cross Apply : Cross apply is basically use inner join of two xml collection

SELECT Child1.value('@Data', 'VARCHAR(MAX)') AS [Child1]
              ,Child2.value('@Data', 'VARCHAR(MAX)') AS [Child2]
           ,Child3.value('@Data', 'VARCHAR(MAX)') AS [Child3]
FROM @Xml.nodes('Child1') d1(Child1)
                CROSS APPLY Child1.nodes('Child2') d2(Child2)
                CROSS APPLY Child2.nodes('Child3') d3(Child3)
Output

Child1	Child2	Child3
Child1-Data	Child2-Data-1	Child3-Data

Outer Apply: Outer apply basically uses left outer join of two XML collections

SELECT Child1.value('@Data', 'VARCHAR(MAX)') AS [Child1]
              ,Child2.value('@Data', 'VARCHAR(MAX)') AS [Child2]
           ,Child3.value('@Data', 'VARCHAR(MAX)') AS [Child3]
FROM @Xml.nodes('Child1') d1(Child1)
                OUTER APPLY Child1.nodes('Child2') d2(Child2)
                OUTER  APPLY Child2.nodes('Child3') d3(Child3)

Output

Child1	Child2	Child3
Child1-Data	Child2-Data-1	Child3-Data
Child1-Data	Child2-Data-2	NULL

Here you can see, we don't have data in Child3 (See Sample XML) for second node
 So In CROSS APPLY It will not select that row.
 and In OUTER APPLY it will select the row but display NULL in that row.