Showing posts with label Outer Apply. Show all posts
Showing posts with label Outer Apply. Show all posts

Thursday, March 15, 2012

Diffrence between CROSS APPLY and OUTER APPLY of SQL XML


Sample XML :
DECLARE @XML XML =
'<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>'


Cross Apply : Cross apply is basically use inner join of two xml collection
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 is basically use left outer join of two xml collection
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