USE [SDS_DevSchoolDistrict]
GO
SELECT dbo.AssessmentTestScoresByStudentAsXml(2678, DEFAULT, 0, 357, 1)
GO
-- =============================================
-- Author:      School Data Solutions
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- Copyright 2011-2012 School Data Solutions, All Rights Reserved
-- =============================================
ALTER FUNCTION [dbo].[AssessmentTestScoresByStudentAsXml]
(   ---------------------------------------------
    @StudentId      int,
    ---------------------------------------------
    @DataViewMode       varchar(200) = 'Standard',
    @EncryptIds         bit = 0,
    @ApplicationUserId  int = NULL,
    @ApplicationUserPersonRoleId int = 1
)   ---------------------------------------------
RETURNS Xml
AS
BEGIN   /*---------------------------------------------
        --SELECT StudentId, COUNT(AssessmentTestId) FROM StudentAssessmentTestScores GROUP BY StudentId
        DECLARE @StudentId                  int = 2678
        DECLARE @DataViewMode       varchar(200)= 'Standard'
        DECLARE @EncryptIds                 bit = 0
        DECLARE @ApplicationUserId          int = 357
        DECLARE @ApplicationUserPersonRoleId int = 1
        ---------------------------------------------*/



        ---------------------------------------------
        -- Declare the return variable here
        -- Prepare for performance metrics
        ---------------------------------------------
        DECLARE @Xml XML = '<Xml xmlAssemblyTimeInMilliseconds=""></Xml>'
        DECLARE @StartTime time = CAST(GETDATE() as time)
        ---------------------------------------------



        ---------------------------------------------
        -- Ensure access is valid
        ---------------------------------------------
        IF NOT EXISTS(
            SELECT  *
            FROM    ApplicationUserPermissions AUP
            JOIN    SecurableItems SI
              ON    SI.SecurableItem = 'Student Assessments'
             AND    AUP.ApplicationSecurityPermissionId >=
                        SI.MinimumApplicationSecurityPermissionId
            WHERE   AUP.EntityId = @StudentId
             AND    AUP.PersonRoleId = 8 --student
             AND    AUP.ApplicationUserId = @ApplicationUserId
             AND    AUP.ApplicationUserPersonRoleId = @ApplicationUserPersonRoleId
            )
            GOTO ALLDONE




        ---------------------------------------------
        -- Student-Assessment-Test-Scores
        ---------------------------------------------
        DECLARE @StudentAssessmentTestScores TABLE (
                AssessmentTestId                int,
                StudentAssessmentTestScore      float, --decimal(18, 2),
                StudentAssessmentTestScoreText  varchar(20),
                AssessmentTestAdministrationId  int,
                SchoolYearId                    int,
                AssessmentTestPerformanceLevelId int,
                AssessmentTestPerformanceLevel  varchar(100),
                AssessmentTestPerformanceLevelColor varchar(20)
        )
        ---------------------------------------------
        INSERT  @StudentAssessmentTestScores
        SELECT  SATS.AssessmentTestId,
                SATS.StudentAssessmentTestScore,
                dbo.TrimZeros(SATS.StudentAssessmentTestScore),
                SATS.AssessmentTestAdministrationId,
                SATS.SchoolYearId,
                PL.AssessmentTestPerformanceLevelId,
                PL.AssessmentTestPerformanceLevel,
                PL.AssessmentTestPerformanceLevelColor
        FROM    StudentMaximumAssessmentTestScores(NULL, @StudentId, NULL) SATS
        LEFT
        JOIN    AssessmentTestPerformanceLevels PL
          ON    SATS.AssessmentTestPerformanceLevelId = PL.AssessmentTestPerformanceLevelId
        WHERE   SATS.StudentId = @StudentId
        ---------------------------------------------
        --SELECT * FROM @StudentAssessmentTestScores
        ---------------------------------------------




        ---------------------------------------------
        -- Assessment-Tests
        ---------------------------------------------
        DECLARE @AssessmentTests TABLE (
                AssessmentTestId [ElementId]  NOT NULL,
                AssessmentTestName [ElementName] NOT NULL,
                AssessmentTestLabel [ElementName] NOT NULL,
                AssessmentTestIsPassFail [bit] NOT NULL,
                LowestPossibleScore [Number] NOT NULL,
                HighestPossibleScore [Number] NOT NULL,
                MinimumPassingScore [Number] NULL,
                PassingScoreColor [HexColor] NOT NULL,
                FailingScoreColor [HexColor] NOT NULL,
        --      GradeLevelId [ElementReference] NULL,
                AssessmentTestSubjectAreaId [ElementReference] NULL,
                ParentAssessmentTestId [SelfReference] NULL,
                CorrespondingAssessmentTestGroupId [ElementReference] NULL
        )
        ---------------------------------------------
        INSERT  @AssessmentTests
        SELECT  T.AssessmentTestId,
                T.AssessmentTestName,
                T.AssessmentTestLabel,
                T.AssessmentTestIsPassFail,
                T.LowestPossibleScore,
                T.HighestPossibleScore,
                T.MinimumPassingScore,
                T.PassingScoreColor,
                T.FailingScoreColor,
        --      T.GradeLevelId,
                T.AssessmentTestSubjectAreaId,
                T.ParentAssessmentTestId,
                T.CorrespondingAssessmentTestGroupId
        FROM    AssessmentTests T
        WHERE   EXISTS (
                        SELECT  1
                        FROM    @StudentAssessmentTestScores SATS
                        WHERE   SATS.AssessmentTestId = T.AssessmentTestId
                )
        ---------------------------------------------
        --SELECT * FROM @AssessmentTests
        ---------------------------------------------






        ---------------------------------------------
        -- @SchoolYears
        ---------------------------------------------
        DECLARE @SchoolYears TABLE (
                SchoolYearId [ElementId]  NOT NULL,
                SchoolYear [ElementName] NOT NULL,
                Ordinal int NULL
        )
        ---------------------------------------------
        INSERT  @SchoolYears
        SELECT  SY.SchoolYearId,
                SY.SchoolYear,
                20 - SY.SchoolYearOrdinal AS Ordinal
        FROM    SchoolYears SY
        WHERE   EXISTS (
                        SELECT 1
                        FROM    @StudentAssessmentTestScores SATS
                        WHERE   SATS.SchoolYearId = SY.SchoolYearId
                )
        ---------------------------------------------
        --SELECT * FROM @SchoolYears
        ---------------------------------------------





        ---------------------------------------------
        -- @SubjectAreas
        ---------------------------------------------
        DECLARE @SubjectAreas TABLE (
                AssessmentTestSubjectAreaId [ElementId]  NOT NULL,
                AssessmentTestSubjectArea [ElementName] NOT NULL,
                Ordinal int NULL,
                SchoolYearId int
        )
        ---------------------------------------------
        INSERT  @SubjectAreas
        SELECT  DISTINCT
                ATSA.AssessmentTestSubjectAreaId,
                ATSA.AssessmentTestSubjectArea,
                ATSA.AssessmentTestSubjectAreaOrdinal,
                SATS.SchoolYearId
        FROM    @StudentAssessmentTestScores SATS
        JOIN    AssessmentTests AT
          ON    SATS.AssessmentTestId = AT.AssessmentTestId
        JOIN    AssessmentTestSubjectAreas ATSA
          ON    AT.AssessmentTestSubjectAreaId = ATSA.AssessmentTestSubjectAreaId
        ---------------------------------------------
        --SELECT * FROM @SubjectAreas
        ---------------------------------------------





        ---------------------------------------------
        -- @AssessmentTestScores
        ---------------------------------------------
        DECLARE @AssessmentTestScores TABLE (
                AssessmentTestId [ElementId]  NOT NULL,
                AssessmentTestLabel [ElementName] NOT NULL,
                OrdinalText varchar(50) NULL,
                AssessmentTestIsPassFail bit,
                CorrespondingAssessmentTestGroupId int,
                HighestPossibleScore int,
                LowestPossibleScore int,
                MinimumPassingScore int,
                PassingScoreColor varchar(6),
                FailingScoreColor varchar(6),
                AssessmentTestSubjectAreaId int,
                ParentAssessmentTestId int,
                --------------------------------------
                StudentAssessmentTestScore decimal(18, 2),
                StudentAssessmentTestScoreText varchar(20),
                SchoolYearId int,
                AssessmentTestPerformanceLevelId int,
                AssessmentTestPerformanceLevel varchar(50),
                AssessmentTestPerformanceLevelColor varchar(6),
                Passed bit

        )
        ---------------------------------------------
        INSERT  @AssessmentTestScores
        SELECT  DISTINCT
                AT.AssessmentTestId,
                AT.AssessmentTestLabel,
                AT.AssessmentTestLabel,
                AT.AssessmentTestIsPassFail,
                AT.CorrespondingAssessmentTestGroupId,
                AT.HighestPossibleScore,
                AT.LowestPossibleScore,
                AT.MinimumPassingScore,
                AT.PassingScoreColor,
                AT.FailingScoreColor,
                AT.AssessmentTestSubjectAreaId,
                AT.ParentAssessmentTestId,
                --------------------------------------
                SATS.StudentAssessmentTestScore,
                SATS.StudentAssessmentTestScoreText,
                SATS.SchoolYearId,
                SATS.AssessmentTestPerformanceLevelId,
                SATS.AssessmentTestPerformanceLevel,
                SATS.AssessmentTestPerformanceLevelColor,
                CASE
                    WHEN(SATS.StudentAssessmentTestScore >= AT.MinimumPassingScore) THEN CAST(1 as bit)
                    ELSE CAST(0 as bit)
                END AS Passed
        FROM    @AssessmentTests AT
        JOIN    @StudentAssessmentTestScores SATS
          ON    AT.AssessmentTestId = SATS.AssessmentTestId
        ---------------------------------------------
        --SELECT * FROM @AssessmentTestScores
        ---------------------------------------------















        ---------------------------------------------
        -- Assessment-Tests-Performance-Levels
        ---------------------------------------------
        DECLARE @PerformanceLevels XML
        SET @PerformanceLevels = (
                SELECT  CASE @EncryptIds
                          WHEN 1
                            THEN CAST(PLEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(PL.AssessmentTestPerformanceLevelId as varchar(200))
                        END AS '@Id',
                        PL.AssessmentTestPerformanceLevel AS '@Label',
                        PL.AssessmentTestPerformanceLevelMinimumScore AS '@MinimumScore',
                        PL.AssessmentTestPerformanceLevelMaximumScore AS '@MaximumScore',
                        PL.AssessmentTestPerformanceLevelColor AS '@Color',
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(AssessmentTestEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(PL.AssessmentTestId as varchar(200))
                        END AS '@AssessmentTestId',
                        ROW_NUMBER() OVER (PARTITION BY PL.AssessmentTestId ORDER BY PL.AssessmentTestPerformanceLevelOrdinalByTest DESC) AS '@Ordinal'
                FROM    AssessmentTestPerformanceLevels PL
                LEFT
                JOIN    EncryptedIds PLEncryptedIds
                  ON    PL.AssessmentTestPerformanceLevelId = PLEncryptedIds.Id
                JOIN    EncryptedIds AssessmentTestEncryptedIds
                  ON    PL.AssessmentTestId = AssessmentTestEncryptedIds.Id
                WHERE   EXISTS (
                                SELECT  1
                                FROM    @StudentAssessmentTestScores SATS
                                WHERE   PL.AssessmentTestId = SATS.AssessmentTestId
                        )
                FOR XML PATH('PerformanceLevel'), ROOT('PerformanceLevels')
        )
        ---------------------------------------------
        --SELECT @PerformanceLevels AS '@PerformanceLevels'
        ---------------------------------------------






        ---------------------------------------------
        -- AssessmentTestScores
        /*---------------------------------------------
        <?xml version="1.0" encoding="UTF-8"?>
        <Xml for="Assessment-Test-Scores-by-Student">
            <SchoolYear SchoolYear="2011/2012" Ordinal="1">
                <SubjectArea SubjectArea="">
                    <AssessmentTest Label="MAP" LowestPossibleScore="200" highestPossibleScore="800" StudentAssesmentTestScore="425" AssessmentTestIsPassFail="False" PassingScoreColor="" FailingScoreColor="" AssessmentTestAdministrationId="1" AssessmentTestAdministrationName="End of year" AssessmentTestAdministrationStartDate="10/10/2012" AssessmentTestAdministrationEndDate="11/11/2012" AssessmentTestAdministrationLocation="Wilson High School">
                        <AssessmentTestPerformanceLevel id="1" name="Level 1" color="#FF0000" minimumScore="" maximumScore="" ordinal=""/>
                        <ChildAssessmentTest  id="1" Label="MAP - Reading" LowestPossibleScore="200" highestPossibleScore="800" StudentAssesmentTestScore="425" AssessmentTestIsPassFail="False" PassingScoreColor="" FailingScoreColor="" AssessmentTestAdministrationId="1" AssessmentTestAdministrationName="End of year" AssessmentTestAdministrationStartDate="10/10/2012" AssessmentTestAdministrationEndDate="11/11/2012" AssessmentTestAdministrationLocation="Wilson High School" SchoolYear="2011/2012">
                            <AssessmentTestPerformanceLevel id="1" name="Level 1" color="#FF0000" minimumScore="" maximumScore="" ordinal=""/>
                        </ChildAssessmentTest>
                    </AssessmentTest>
                </SubjectArea>
            </SchoolYear>
        </Xml>
        */---------------------------------------------
        DECLARE @SchoolYearsSubjectAreasAssessmentTests Xml
        ---------------------------------------------
        SET @SchoolYearsSubjectAreasAssessmentTests = (
                SELECT  -------------------------
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(SchoolYearEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(SchoolYear.SchoolYearId as varchar(200))
                        END AS 'SchoolYear/@Id',
                        SchoolYear.SchoolYear AS 'SchoolYear/@Text',
                        SchoolYear.Ordinal AS 'SchoolYear/@Ordinal',
                        -------------------------
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(SubjectAreaEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(SubjectArea.AssessmentTestSubjectAreaId as varchar(200))
                        END AS 'SchoolYear/SubjectArea/@Id',
                        SubjectArea.AssessmentTestSubjectArea AS 'SchoolYear/SubjectArea/@Text',
                        SubjectArea.Ordinal AS 'SchoolYear/SubjectArea/@Ordinal',
                        -------------------------
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(AssessmentTestEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(AssessmentTest.AssessmentTestId as varchar(200))
                        END AS 'SchoolYear/SubjectArea/AssessmentTest/@Id',
                        AssessmentTest.AssessmentTestLabel AS 'SchoolYear/SubjectArea/AssessmentTest/@Label',
                        AssessmentTest.AssessmentTestLabel AS 'SchoolYear/SubjectArea/AssessmentTest/@Ordinal',
                        AssessmentTest.AssessmentTestIsPassFail AS 'SchoolYear/SubjectArea/AssessmentTest/@IsPassFail',
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(AssessmentTestGroupEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(AssessmentTest.CorrespondingAssessmentTestGroupId as varchar(200))
                        END AS 'SchoolYear/SubjectArea/AssessmentTest/@AssessmentTestGroupId',
                        AssessmentTest.HighestPossibleScore AS 'SchoolYear/SubjectArea/AssessmentTest/@MaximumScore',
                        AssessmentTest.LowestPossibleScore AS 'SchoolYear/SubjectArea/AssessmentTest/@MinimumScore',
                        AssessmentTest.MinimumPassingScore AS 'SchoolYear/SubjectArea/AssessmentTest/@MinimumPassingScore',
                        AssessmentTest.PassingScoreColor AS 'SchoolYear/SubjectArea/AssessmentTest/@PassingScoreColor',
                        AssessmentTest.FailingScoreColor AS 'SchoolYear/SubjectArea/AssessmentTest/@FailingScoreColor',
                        --AssessmentTest.StudentAssessmentTestScore AS 'Score',
                        AssessmentTest.StudentAssessmentTestScoreText AS 'SchoolYear/SubjectArea/AssessmentTest/@Score',
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(AssessmentTestPLEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(AssessmentTest.AssessmentTestPerformanceLevelId as varchar(200))
                        END AS 'SchoolYear/SubjectArea/AssessmentTest/@PerformanceLevelId',
                        AssessmentTest.AssessmentTestPerformanceLevel AS 'SchoolYear/SubjectArea/AssessmentTest/@PerformanceLevel',
                        AssessmentTest.AssessmentTestPerformanceLevelColor AS 'SchoolYear/SubjectArea/AssessmentTest/@PerformanceLevelColor',
                        AssessmentTest.Passed AS 'SchoolYear/SubjectArea/AssessmentTest/@Passed',
                        -------------------------
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(ChildAssessmentTestEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(ChildAssessmentTest.AssessmentTestId as varchar(200))
                        END AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@Id',
                        ChildAssessmentTest.AssessmentTestLabel AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@Label',
                        ChildAssessmentTest.AssessmentTestLabel AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@Ordinal',
                        ChildAssessmentTest.AssessmentTestIsPassFail AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@IsPassFail',
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(ChildAssessmentTestGroupEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(ChildAssessmentTest.CorrespondingAssessmentTestGroupId as varchar(200))
                        END AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@AssessmentTestGroupId',
                        ChildAssessmentTest.HighestPossibleScore AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@MaximumScore',
                        ChildAssessmentTest.LowestPossibleScore AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@MinimumScore',
                        ChildAssessmentTest.MinimumPassingScore AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@MinimumPassingScore',
                        ChildAssessmentTest.PassingScoreColor AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@PassingScoreColor',
                        ChildAssessmentTest.FailingScoreColor AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@FailingScoreColor',
                        --ChildAssessmentTest.StudentAssessmentTestScore AS 'Score',
                        ChildAssessmentTest.StudentAssessmentTestScoreText AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@Score',
                        CASE @EncryptIds
                          WHEN 1
                            THEN CAST(ChildAssessmentTestPLEncryptedIds.EncryptedId as varchar(200))
                          ELSE   CAST(ChildAssessmentTest.AssessmentTestPerformanceLevelId as varchar(200))
                        END AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@PerformanceLevelId',
                        ChildAssessmentTest.AssessmentTestPerformanceLevel AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@PerformanceLevel',
                        ChildAssessmentTest.AssessmentTestPerformanceLevelColor AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@PerformanceLevelColor',
                        ChildAssessmentTest.Passed AS 'SchoolYear/SubjectArea/AssessmentTest/ChildAssessmentTest/@Passed'
                        -------------------------
                FROM    @SchoolYears SchoolYear
                JOIN    EncryptedIds SchoolYearEncryptedIds
                  ON    SchoolYear.SchoolYearId = SchoolYearEncryptedIds.Id
                JOIN    @SubjectAreas SubjectArea
                  ON    SchoolYear.SchoolYearId = SubjectArea.SchoolYearId
                JOIN    EncryptedIds SubjectAreaEncryptedIds
                  ON    SubjectArea.AssessmentTestSubjectAreaId = SubjectAreaEncryptedIds.Id

                --      Parent test and associated encrypted ids
                JOIN    @AssessmentTestScores AssessmentTest
                  ON    AssessmentTest.SchoolYearId = SchoolYear.SchoolYearId
                 AND    AssessmentTest.AssessmentTestSubjectAreaId = SubjectArea.AssessmentTestSubjectAreaId
                 AND    AssessmentTest.ParentAssessmentTestId IS NULL
                JOIN    EncryptedIds AssessmentTestEncryptedIds
                  ON    AssessmentTest.AssessmentTestId = AssessmentTestEncryptedIds.Id
                JOIN    EncryptedIds AssessmentTestPLEncryptedIds
                  ON    AssessmentTest.AssessmentTestPerformanceLevelId = AssessmentTestPLEncryptedIds.Id
                LEFT
                JOIN    EncryptedIds AssessmentTestGroupEncryptedIds
                  ON    AssessmentTest.CorrespondingAssessmentTestGroupId = AssessmentTestGroupEncryptedIds.Id

                --      Child tests and assorted encrypted ids
                LEFT
                JOIN    @AssessmentTestScores ChildAssessmentTest
                  ON    AssessmentTest.SchoolYearId = ChildAssessmentTest.SchoolYearId
                 AND    AssessmentTest.AssessmentTestId = ChildAssessmentTest.ParentAssessmentTestId
                LEFT
                JOIN    EncryptedIds ChildAssessmentTestEncryptedIds
                  ON    ChildAssessmentTest.AssessmentTestId = ChildAssessmentTestEncryptedIds.Id
                LEFT
                JOIN    EncryptedIds ChildAssessmentTestPLEncryptedIds
                  ON    ChildAssessmentTest.AssessmentTestPerformanceLevelId = ChildAssessmentTestPLEncryptedIds.Id
                LEFT
                JOIN    EncryptedIds ChildAssessmentTestGroupEncryptedIds
                  ON    ChildAssessmentTest.CorrespondingAssessmentTestGroupId = ChildAssessmentTestGroupEncryptedIds.Id

                ORDER
                   BY   SchoolYear.Ordinal,
                        SubjectArea.Ordinal,
                        AssessmentTest.OrdinalText,
                        ChildAssessmentTest.OrdinalText
                FOR XML PATH(''), ROOT('AssessmentTestScores')
        )
        ---------------------------------------------
        SET @SchoolYearsSubjectAreasAssessmentTests = dbo.XmlNormalize(@SchoolYearsSubjectAreasAssessmentTests)
        --SELECT @SchoolYearsSubjectAreasAssessmentTests AS '@SchoolYearsSubjectAreasAssessmentTests'
        ---------------------------------------------





        ---------------------------------------------
        -- Student
        ---------------------------------------------
        DECLARE @Student XML
        SET @Student = (
                SELECT  P.FirstName AS '@FirstName',
                        P.LastName AS '@LastName',
                        CASE
                            WHEN(@EncryptIds = 1) THEN P.EncryptedId
                            ELSE P.PersonId
                        END AS '@Id'
                FROM    DataModePersons(@StudentId, @DataViewMode) P
                FOR XML PATH('Student'), ROOT('Xml')
        )
        ---------------------------------------------
        --SELECT @Student AS '@Student'
        ---------------------------------------------






        ---------------------------------------------
        -- Assemble output Xml
        ---------------------------------------------
        SET @SchoolYearsSubjectAreasAssessmentTests = dbo.XmlJoin(@SchoolYearsSubjectAreasAssessmentTests, @PerformanceLevels, 'AssessmentTest.Id = PerformanceLevel.AssessmentTestId')
        SET @SchoolYearsSubjectAreasAssessmentTests = dbo.XmlJoin(@SchoolYearsSubjectAreasAssessmentTests, @PerformanceLevels, 'ChildAssessmentTest.Id = PerformanceLevel.AssessmentTestId')
        SET @Student = dbo.XmlAdopt(@Student,@SchoolYearsSubjectAreasAssessmentTests,'Student','SchoolYear')
        SET @Xml = dbo.XmlAdopt(@Xml,@Student,'Xml','Student')
        ---------------------------------------------


ALLDONE:

        ---------------------------------------------
        -- Track Assembly time
        ---------------------------------------------
        DECLARE @XmlAssemblyTimeInMilliseconds int = DATEDIFF(ms, @StartTime,  CAST(GETDATE() as time))
        SET @Xml = dbo.XmlAssignAttributeValues(@Xml, '//Xml', 'xmlAssemblyTimeInMilliseconds', @XmlAssemblyTimeInMilliseconds)
        ---------------------------------------------


        --SELECT @Xml AS '@Xml'
        --------------------------------------------
        RETURN @Xml
END