Page History: Using the CROSS APPLY Syntax - SQL Server

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Wed, Nov 25, 2009, 12:09 PM

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