Thursday, April 22, 2010

Using SQL Server CROSS APPLY to read XML segment

Here is an interesting code snippet:

declare @XML xml
set @XML ='<students>
<student name="Julius">
<subjects>
<subject name="Maths" score="90"></subject>
<subject name="Art" score="50"></subject>
<subject name="English" score="70"></subject>
</subjects>
</student>
<student name="Becky">
<subjects>
<subject name="Maths" score="90"></subject>
<subject name="Art" score="50"></subject>
<subject name="English" score="70"></subject>
</subjects>'
select ExpressionAlias.student_name,
ExpressionAlias.subject_name,
ExpressionAlias.score
From @XML.nodes('./students/student') Student (rowset)
Cross Apply
Student.rowset.nodes('./subjects/subject') Subject (rowset)
Cross Apply (
Select Student.rowset.value('@name','NVARCHAR(20)'),
Subject.rowset.value('@name','NVARCHAR(20)'),
Subject.rowset.value('@score','INTEGER')
) ExpressionAlias (student_name, subject_name,score)
Order By
ExpressionAlias.student_name ASC,
ExpressionAlias.score DESC;

3 comments:

Suresh said...

CORRECT XML

declare @XML xml;
set @XML = N'














'

Suresh said...

CORRECT XML
declare @XML xml;
set @XML = N'














'

Julius A said...

Thanks @Suresh for correction. Actually the sample xml is also missing two closing tags, i.e.