Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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