Subscribe To

Subscribe to SQL Server and XML by Email
Labels

Thursday, June 26, 2008

XQuery Lab 2 - An example using OUTER APPLY

This post is moved to http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-lab-2-an-example-using-outer-apply.aspx

2 comments:

  1. Can any one tell me what will be the query if I store this xml into database table (say “Pupiltable”) xml column (say “pupilcolumn”)?
    I want to set @x value from database table.
    I use following code for set @x value.But it dose not work.

    DECLARE @x XML
    SELECT @x= pupilcolumn from Pupiltable
    SELECT
    Pupil.value('PupilID[1]' ,'varchar(15)') as UPN,
    Pupil.value('Surname[1]' ,'varchar(10)') as Surname,
    Pupil.value('Forename[1]' ,'varchar(10)') as Forename,
    att.value('SessionsPossible[1]' ,'int') as SessionsPossible,
    det.value('AttendanceReason[1]' ,'varchar(5)') as AttendanceReason,
    det.value('AbsenceSessions[1]' ,'varchar(5)') as AttendanceSessions
    FROM
    @x.nodes('//Pupils/Pupil/PupilIdentifiers') Pupils(Pupil)
    OUTER APPLY Pupil.nodes('../Attendance/TermlyAttendance') Term(att)
    OUTER APPLY att.nodes('SessionDetails/SessionDetail') Sess(det)

    thanks

    ReplyDelete
  2. Here is an example that shows how to load the value of an XML column to a variable.

    I hope this will help you.

    -- create a table with an XML column
    DECLARE @t TABLE (data XML)

    -- insert a row with an XML value
    INSERT INTO @t SELECT (
    SELECT
    'test' AS val
    FOR XML PATH(''), ROOT('data')
    )

    -- DECLARE an XML variable
    DECLARE @x XML

    -- load the XML value to the variable
    SELECT
    @x = data
    FROM @t

    -- read values from the XML variable
    SELECT @x.value('(data/val)[1]', 'VARCHAR(20)') AS val

    /*
    val
    --------------------
    test
    */

    ReplyDelete

About Me
Jacob Sebastian
Tutorials
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Readers
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
 
Copyright Jacob Sebastian