SQL "For XML Path" - nested results
It can be done.
select a1.YearPart as '@Year', ( select MonthPart as '@Month', (select DayPart as '@Day', (select lib_title as '@RecordName', PID as '@RecordID' from @agenda as a4 where a4.DayPart = a3.DayPart and a4.MonthPart = a2.MonthPart and a4.YearPart = a1.YearPart for xml path('Item'), type ) from @agenda as a3 where a3.YearPart = a1.YearPart and a3.MonthPart = a2.MonthPart group by a3.DayPart for xml path('Day'), type ) from @agenda as a2 where a1.YearPart = a2.YearPart group by a2.MonthPart for xml path('Month'), type ) from @agenda as a1group by YearPartfor xml path('Year'), root