See if this gets you close:

SELECT sp.StudentID AS [@id],

(

SELECT 'final' AS type,

(

SELECT (

SELECT 'catalog' AS type,

CAST(CAST(path.Catalog AS NVARCHAR(40)) AS XML)

FROM dbo.StudentPrograms path

WHERE sp.StudentID = path.StudentID

FOR

XML AUTO,

TYPE

),

(

SELECT 'degree' AS type,

CAST(RTRIM(path.Degree) AS XML)

FROM dbo.StudentPrograms path

WHERE sp.StudentID = path.StudentID

AND path.Degree != ''

FOR

XML AUTO,

TYPE

),

(

SELECT 'major' AS type,

CAST(RTRIM(path.Major) AS XML)

FROM dbo.StudentPrograms path

WHERE sp.StudentID = path.StudentID

AND path.Major != ''

FOR

XML AUTO,

TYPE

),

(

SELECT 'minor' AS type,

CAST(RTRIM(path.Minor) AS XML)

FROM dbo.StudentPrograms path

WHERE sp.StudentID = path.StudentID

AND path.Minor != ''

FOR

XML AUTO,

TYPE

),

(

SELECT 'concentration' AS type,

CAST(RTRIM(path.Concentration) AS XML)

FROM dbo.StudentPrograms path

WHERE sp.StudentID = path.StudentID

AND path.Concentration != ''

FOR

XML AUTO,

TYPE

)

FROM dbo.StudentPrograms sp

FOR

XML PATH('student_path'),

TYPE

)

FROM dbo.StudentPrograms educational_goal

WHERE sp.StudentID = educational_goal.StudentID

FOR

XML AUTO,

TYPE

)

FROM dbo.StudentPrograms sp

FOR XML PATH('student');