FOR XML PATH with TYPE is much better than XML EXPLICIT

Just dorking with XML for a bit, didn't you really abhor the old XML EXPLICIT back a few years ago?  I sure did, and we got it much better now.  Run the following:

DROP TABLE ClassXML_Student, ClassXML_StudentCourse
GO
CREATE TABLE ClassXML_Student
    (StudentID int
    ,StudentFirst varchar (40)
    ,StudentLast varchar (40)
    ,EnrollDt datetime
    )
GO
CREATE TABLE ClassXML_StudentCourse
    (CourseID int
    ,StudentID int
    ,CourseDescription varchar (400)
    )
GO

INSERT ClassXML_Student VALUES (1,'Joe', 'Schmo', '1/1/2010')
                ,(2,'Sally', 'Smith', '1/1/2010')
                ,(3,'Jack', 'Jones', '1/1/2010')

INSERT ClassXML_StudentCourse VALUES (100,1,'Math')
INSERT ClassXML_StudentCourse VALUES (200,1,'Physics')

Now let's compare what we had to what we have now.  There is no comparison:

SELECT 1 as Tag
        ,NULL as Parent
        ,Student.StudentID As [Student!1!StudentID!ELEMENT]
        ,NULL AS [Course!2!CourseID!ELEMENT]        
        ,NULL AS [Course!2!CourseDescription!ELEMENT]        
FROM ClassXML_Student Student
UNION ALL
SELECT 2  
        ,1  
        ,Student.StudentID
        ,Course.CourseID
        ,Course.CourseDescription
FROM ClassXML_Student Student
INNER JOIN ClassXML_StudentCourse Course
    ON Student.StudentID = Course.StudentID
order by [Student!1!StudentID!ELEMENT], [Course!2!CourseID!ELEMENT]
FOR XML EXPLICIT 

SELECT StudentID
    ,(SELECT CourseID 
            ,CourseDescription
        from ClassXML_StudentCourse c
        WHERE c.StudentID = s.StudentID
        FOR XML PATH('Course'), TYPE)
FROM ClassXML_Student s
FOR XML PATH('Student')

These two queries return the exact same thing (I checked it about half-dozen times); so how much easier is the second than the first?  The old way - cheesy and slow UNION ALL that forces you to call two queries essentially, all of the levels and tags and other syntax, and this is a simple one.  Not only that, if you happen to forget the ORDER BY in your XML EXPLICIT syntax, you're basically hosed. Try it out.  Contrast to the second, it's a simple correlated subquery in the SELECT statement...no problemo!

Lee Everest

----------------

He's not a man - a machine. A Terminator. A Cyberdyne Systems Model 101.