Difference between CROSS APPLY and OUTER APPLY of SQL XML
The difference between CROSS APPLY and OUTER APPLY of SQL XML
CodeProject
Introduction
The APPLY
operator comes in two variants, CROSS APPLY
and OUTER APPLY
. It is useful for joining two SQL tables or XML expressions. CROSS APPLY
is equivalent to an INNER JOIN
expression and OUTER APPLY
is equivalent to a LEFT OUTER JOIN
expression.
Solution
Sample XML
DECLARE @XML XML =
<span class="str">'<Root>
<UserInfo Id="1" Name="Name 1">
<CityInfo Id="2" City="City 1" />
<CityInfo Id="3" City="City 2" >
<PrefInfo Id="33" Name="Show Whether" />
</CityInfo>
</UserInfo>
<UserInfo Id="2" Name="Name 2">
<CityInfo Id="4" City="City 3" />
<CityInfo Id="5" City="City 4" >
<PrefInfo Id="33" Name="Show Temprature" />
</CityInfo>
</UserInfo>
</Root>'</span>
Cross Apply
Cross apply
basically uses inner join
of two XML collections
SELECT
UserInfo.value('@Id', 'BIGINT') as UserId
, UserInfo.value('@Name', 'VARCHAR(20)') as UserName
, CityInfo.value('@Id', 'BIGINT') as CityId
, CityInfo.value('@City', 'VARCHAR(20)') as CityName
, PrefInfo.value('@Id', 'BIGINT') as PrefId
, PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
CROSS APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)
Output
UserId UserName CityId CityName PrefId PrefName
-------- --------- ------- --------- -------- ----------------
1 Name 1 3 City 2 33 Show Whether
2 Name 2 5 City 4 33 Show Temprature
Outer Apply
Outer apply
basically uses left outer join
of two XML collections
SELECT
UserInfo.value('@Id', 'BIGINT') as UserId
, UserInfo.value('@Name', 'VARCHAR(20)') as UserName
, CityInfo.value('@Id', 'BIGINT') as CityId
, CityInfo.value('@City', 'VARCHAR(20)') as CityName
, PrefInfo.value('@Id', 'BIGINT') as PrefId
, PrefInfo.value('@Name', 'VARCHAR(20)') as PrefName
FROM @xml.nodes('/Root/UserInfo')e(UserInfo)
CROSS APPLY UserInfo.nodes('CityInfo')b(CityInfo)
OUTER APPLY CityInfo.nodes('PrefInfo')c(PrefInfo)
Output
UserId UserName CityId CityName PrefId PrefName
------- --------- ------- --------- ------- ----------------
1 Name 1 2 City 1 NULL NULL
1 Name 1 3 City 2 33 Show Whether
2 Name 2 4 City 3 NULL NULL
2 Name 2 5 City 4 33 Show Temprature