Tuesday, March 12, 2013

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.