Jasinski Technical Wiki

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

PoweredBy

Page History: Using the CROSS APPLY Syntax - SQL Server

Compare Page Revisions



« 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2024, Patrick Jasinski.