Using the CROSS APPLY Syntax - SQL Server

Requirement¶

Database version must be at least SQL Server 2005 with compatibility set to at least 90. You can check database compatibility level as shown here.

Basic Example¶

select A.*, b.X
from A
cross apply dbo.UDF(A.Val) b

Another Example¶

The database has a view called SongExtended with an XML field called Lyrics, which has a layout like this.

<song>
  <title>Some Song</title>
  <section>
    <name>Chorus</name>
    <line>A line of lyrics</line>
    <line>A second line of lyrics</line>
    . . .
  </section>
  . . .
</song>

The SongExtended view yields one row per song, but I wanted one row per line of lyrics. Here's how I did it...

select
     SongId
    ,Lyric = T.c.value('.','varchar(max)')
from
    SongExtended
    cross apply Lyrics.nodes('/song/section/line') T(c)
where 1=1
    and Lyrics is not null