Using the CROSS APPLY Syntax - SQL Server

Requirement


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