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

Originally posted @ JSD Ideas[^]

Difference between CROSS APPLY and OUTER APPLY of SQL XML