· 7 years ago · Oct 17, 2018, 12:10 PM
1USE [SDS_DevSchoolDistrict]
2GO
3/****** Object: UserDefinedFunction [dbo].[AssessmentTestGroupScoresByStudentGroupAsXml] Script Date: 09/05/2012 17:55:16 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10
11-- =============================================
12-- Author: School Data Solutions
13-- Create date: <Create Date, ,>
14-- Description: <Description, ,>
15-- Copyright 2011-2012 School Data Solutions, All Rights Reserved
16-- ============================================
17ALTER FUNCTION [dbo].[AssessmentTestGroupScoresByStudentGroupAsXml]
18(
19 @StudentGroupId int,
20 @AssessmentTestGroupId int,
21
22 @AssessmentTestIdList varchar(1000) = NULL,
23 @OrderingAssessmentTestId int = NULL,
24 @OrderingDirection varchar(4) = NULL,
25
26 @DefaultAssessmentCount int = 8,
27 @StudentPageIndex int = 1,
28 @StudentPageSize int = 400,
29
30 @DataViewMode varchar(200) = 'Standard',
31 @EncryptIds bit = 0,
32 @ApplicationUserId int,
33 @ApplicationUserPersonRoleId int
34)
35RETURNS Xml
36AS
37BEGIN /*--------------------------------------------
38 -- Debugging
39 ---------------------------------------------
40 DECLARE @StudentGroupId int = 573172 --
41 DECLARE @AssessmentTestGroupId int = 54 -- 12 -- 14
42 ---------------------------------------------
43 DECLARE @AssessmentTestIdList varchar(1000) --= '66, 67, 7, 68, 14'
44 DECLARE @OrderingAssessmentTestId int --= 67
45 DECLARE @OrderingDirection varchar(4) --= 'ASC'
46 DECLARE @DefaultAssessmentCount int = 10
47 ---------------------------------------------
48 DECLARE @StudentPageIndex int = 1
49 DECLARE @StudentPageSize int = 400 -- 200: 480ms; 500
50 ---------------------------------------------
51 DECLARE @DataViewMode varchar(200) = 'Standard'
52 DECLARE @EncryptIds bit = 0
53 DECLARE @ApplicationUserId int = 357
54 DECLARE @ApplicationUserPersonRoleId int = 1
55-- SELECT dbo.AssessmentTestGroupScoresByStudentGroupAsXml(51315, 54, NULL, NULL, NULL, DEFAULT, DEFAULT, DEFAULT, DEFAULT, 0, 357, 1)
56 ---------------------------------------------*/
57
58
59 ---------------------------------------------
60 -- Declare the return variable here
61 -- Prepare for performance metrics
62 ---------------------------------------------
63 DECLARE @Xml XML = '<Xml/>'
64 DECLARE @StartTime time = CAST(GETDATE() as time)
65 ---------------------------------------------
66
67
68
69
70
71
72 ---------------------------------------------
73 -- If no @OrderingDirection passed
74 -- default order names ASC (ALphabetical) and score DESC (high-to-low)
75 ---------------------------------------------
76 IF(@OrderingDirection IS NULL)
77 BEGIN
78 IF(@OrderingAssessmentTestId IS NULL)
79 SET @OrderingDirection = 'ASC' -- for names
80 ELSE
81 SET @OrderingDirection = 'DESC' -- for scores
82 END
83 ---------------------------------------------
84
85
86 ---------------------------------------------
87 -- Assessment-Test-Ids
88 -- We include alternate tests using ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId)
89 ---------------------------------------------
90 DECLARE @AssessmentTestIds TABLE (
91 AssessmentTestGroupId int,
92 AssessmentTestId int,
93 AssessmentTestOrdinal int,
94 PossiblyEncryptedAssessmentTestId varchar(500),
95 -- IsAlternate bit,
96 ScoreCount int DEFAULT(0),
97 SendScores bit DEFAULT(0),
98 -----------------------------------
99 PRIMARY KEY (AssessmentTestId)
100 )
101 ---------------------------------------------
102 INSERT @AssessmentTestIds(
103 AssessmentTestGroupId,
104 AssessmentTestId,
105 AssessmentTestOrdinal,
106 PossiblyEncryptedAssessmentTestId
107 -- IsAlternate
108 )
109 SELECT AT_G.AssessmentTestGroupId,
110 AT.AssessmentTestId,
111 AT_G.AssessmentTestOrdinalByGroup,
112 AssessmentTestEncryptedIds.EncryptedId
113 -- CASE WHEN(AT.UseAsAlternateForAssessmentTestId IS NOT NULL) THEN 1 ELSE 0 END
114 FROM AssessmentTests_Groups AT_G
115 JOIN AssessmentTests AT
116 ON AT.AssessmentTestId = AT_G.AssessmentTestId
117 --ON ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId) = AT_G.AssessmentTestId
118 JOIN EncryptedIds AssessmentTestEncryptedIds
119 ON AT.AssessmentTestId = AssessmentTestEncryptedIds.Id
120 WHERE AT_G.AssessmentTestGroupId = @AssessmentTestGroupId
121 --AND AT_G.AssessmentTestId IN (7, 75, 76)
122 ---------------------------------------------
123 --SELECT * FROM @AssessmentTestIds ORDER BY AssessmentTestOrdinalByGroup
124 ---------------------------------------------
125
126
127
128
129
130
131
132
133
134 ---------------------------------------------
135 -- Student Maximum Assessment Test Scores
136 ---------------------------------------------
137 DECLARE @StudentMaximumAssessmentTestScores TABLE (
138 StudentId int,
139 AssessmentTestId int,
140 StudentAssessmentTestScoreId int,
141 StudentAssessmentTestScore money,
142 AssessmentTestPerformanceLevelId int,
143 StudentAssessmentTestScoreRank int,
144 AssessmentTestOrdinalByGroup int,
145 -----------------------------------
146 PRIMARY KEY (AssessmentTestId, StudentId, StudentAssessmentTestScoreRank)
147 )
148 INSERT @StudentMaximumAssessmentTestScores(
149 StudentId,
150 AssessmentTestId,
151 StudentAssessmentTestScoreId,
152 StudentAssessmentTestScore,
153 AssessmentTestPerformanceLevelId,
154 StudentAssessmentTestScoreRank,
155 AssessmentTestOrdinalByGroup
156 )
157 SELECT SATS.StudentId,
158 -- ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId),
159 AT.AssessmentTestId,
160 SATS.StudentAssessmentTestScoreId,
161 SATS.StudentAssessmentTestScore,
162 SATS.AssessmentTestPerformanceLevelId,
163 ROW_NUMBER() OVER(
164 -- PARTITION BY ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId), SATS.StudentId
165 PARTITION BY AT.AssessmentTestId, SATS.StudentId
166 ORDER BY SATS.StudentAssessmentTestScore DESC,
167 SATS.StudentAssessmentTestScoreId DESC) AS ScoreRank,
168 ATI.AssessmentTestOrdinal
169 FROM @AssessmentTestIds ATI
170 JOIN StudentAssessmentTestScores SATS
171 ON SATS.AssessmentTestId = ATI.AssessmentTestId
172 JOIN AssessmentTests AT
173 ON AT.AssessmentTestId = ATI.AssessmentTestId
174 JOIN Students_Groups S_G
175 ON SATS.StudentId = S_G.StudentId
176 AND S_G.StudentGroupId = @StudentGroupId
177
178 -- Ensure the Application User has access to view assessment data for each students
179 JOIN ApplicationUserPermissions AUP
180 ON S_G.StudentId = AUP.EntityId
181 AND AUP.PersonRoleId = 8 --student
182 AND AUP.ApplicationUserId = @ApplicationUserId
183 AND AUP.ApplicationUserPersonRoleId = @ApplicationUserPersonRoleId
184 JOIN SecurableItems SI
185 ON SI.SecurableItem = 'Student Assessments'
186 AND AUP.ApplicationSecurityPermissionId >=
187 SI.MinimumApplicationSecurityPermissionId
188 ---------------------------------------------
189 --SELECT * FROM @StudentMaximumAssessmentTestScores SMATS
190 ---------------------------------------------
191
192
193 ---------------------------------------------
194 -- Student Assessment Test Scores
195 ---------------------------------------------
196 DECLARE @StudentAssessmentTestScores TABLE (
197 StudentId int,
198 AssessmentTestId int,
199 StudentAssessmentTestScore money,
200 AssessmentTestOrdinal int,
201 AssessmentTestPerformanceLevelId int,
202 AssessmentTestPerformanceLevel varchar(100),
203 AssessmentTestPerformanceLevelColor varchar(6),
204 IsPassFail bit,
205 Passed bit,
206 -----------------------------------
207 PRIMARY KEY (AssessmentTestId, StudentId)
208 )
209 ---------------------------------------------
210 INSERT @StudentAssessmentTestScores (
211 StudentId,
212 AssessmentTestId,
213 StudentAssessmentTestScore,
214 AssessmentTestOrdinal,
215 AssessmentTestPerformanceLevelId,
216 AssessmentTestPerformanceLevel,
217 AssessmentTestPerformanceLevelColor,
218 IsPassFail,
219 Passed
220 )
221 SELECT SATS.StudentId,
222 SATS.AssessmentTestId,
223 SATS.StudentAssessmentTestScore,
224 ATI.AssessmentTestOrdinal,
225 SATS.AssessmentTestPerformanceLevelId,
226 ATPL.AssessmentTestPerformanceLevel,
227 ATPL.AssessmentTestPerformanceLevelColor,
228 AT.AssessmentTestIsPassFail,
229 CASE
230 WHEN(SATS.StudentAssessmentTestScore >= AT.MinimumPassingScore)
231 THEN 1
232 ELSE 0
233 END
234 FROM @StudentMaximumAssessmentTestScores SATS
235 JOIN @AssessmentTestIds ATI
236 ON SATS.AssessmentTestId = ATI.AssessmentTestId
237 AND SATS.StudentAssessmentTestScoreRank = 1
238 JOIN AssessmentTests AT
239 ON ATI.AssessmentTestId = AT.AssessmentTestId
240 LEFT
241 JOIN AssessmentTestPerformanceLevels ATPL
242 ON SATS.AssessmentTestPerformanceLevelId = ATPL.AssessmentTestPerformanceLevelId
243 ---------------------------------------------
244 --SELECT * FROM @StudentAssessmentTestScores ORDER BY StudentId, AssessmentTestId
245 ---------------------------------------------
246
247
248 ---------------------------------------------
249 -- Assessment-Test-Score-Counts
250 ---------------------------------------------
251 DECLARE @AssessmentTestScoreCounts TABLE (
252 AssessmentTestId int,
253 ScoreCount int,
254 -----------------------------------
255 PRIMARY KEY (AssessmentTestId)
256 )
257 ---------------------------------------------
258 INSERT @AssessmentTestScoreCounts(
259 AssessmentTestId,
260 ScoreCount
261 )
262 SELECT SATS.AssessmentTestId, COUNT(SATS.StudentId) AS ScoreCount
263 FROM @StudentAssessmentTestScores SATS
264 GROUP
265 BY SATS.AssessmentTestId
266 ---------------------------------------------
267 --SELECT * FROM @AssessmentTestScoreCounts
268 ---------------------------------------------
269
270
271 ---------------------------------------------
272 -- Update Score Counts per Test
273 ---------------------------------------------
274 UPDATE T
275 SET ScoreCount = C.ScoreCount
276 --SELECT T.AssessmentTestId, C.ScoreCount
277 FROM @AssessmentTestIds T
278 JOIN @AssessmentTestScoreCounts C
279 ON T.AssessmentTestId = C.AssessmentTestId
280 ---------------------------------------------
281 --SELECT * FROM @AssessmentTestIds
282 ---------------------------------------------
283
284
285 ---------------------------------------------
286 -- Delete Assessment test id, where no scores exist for students in passed Student-Group
287 ---------------------------------------------
288 --DELETE @AssessmentTestIds
289 --WHERE ScoreCount = 0
290 ---------------------------------------------
291 --SELECT * FROM @AssessmentTestIds
292 ---------------------------------------------
293
294
295
296 ---------------------------------------------
297 -- If a list of tests was not passed in,
298 -- Search for previously saved selections
299 ---------------------------------------------
300 IF(@AssessmentTestIdList IS NULL)
301 BEGIN
302 SELECT @AssessmentTestIdList = SS.AssessmentTestIdList
303 FROM ApplicationUserAssessmentTestSetSelections SS
304 WHERE SS.ApplicationUserId = @ApplicationUserId
305 AND SS.AssessmentTestGroupId = @AssessmentTestGroupId
306 AND SS.StudentGroupId = @StudentGroupId
307 END
308
309
310
311
312 ---------------------------------------------
313 -- If a list of tests was passed in, or retrieved, use it
314 ---------------------------------------------
315 IF(ISNULL(@AssessmentTestIdList, '') <> '')
316 BEGIN -------------------------------------------------
317 DECLARE @SplitValues TABLE ( AssessmentTestId varchar(500), Ordinal int IDENTITY(1, 1) )
318 -------------------------------------------------
319 INSERT @SplitValues(AssessmentTestId)
320 SELECT CAST(Value as int)
321 FROM dbo.SplitList(@AssessmentTestIdList, ',')
322 --SELECT * FROM @SplitValues SV
323 -------------------------------------------------
324 UPDATE T
325 SET SendScores = O.SendScores,
326 AssessmentTestOrdinal = O.Ordinal
327 --SELECT T.AssessmentTestOrdinal, O.Ordinal,
328 -- T.SendScores, O.SendScores
329 FROM @AssessmentTestIds T
330 JOIN (
331 SELECT ATI.AssessmentTestId,
332 CASE WHEN(SV.Ordinal IS NOT NULL) THEN 1
333 ELSE 0
334 END AS 'SendScores',
335 ROW_NUMBER() OVER(ORDER BY ISNULL(SV.Ordinal, 99999), ATI.AssessmentTestOrdinal) AS 'Ordinal'
336 FROM @AssessmentTestIds ATI
337 LEFT
338 JOIN @SplitValues SV
339 ON ATI.AssessmentTestId = SV.AssessmentTestId
340 --ORDER
341 -- BY ISNULL(SV.Ordinal, 99999), ATI.AssessmentTestOrdinal
342 ) O
343 ON T.AssessmentTestId = O.AssessmentTestId
344 END -------------------------------------------------
345 ELSE
346 BEGIN -------------------------------------------------
347 UPDATE T
348 SET SendScores = CASE WHEN(O.Ordinal <= @DefaultAssessmentCount) THEN 1 ELSE 0 END,
349 AssessmentTestOrdinal = O.Ordinal
350 --SELECT T.AssessmentTestOrdinal, O.Ordinal,
351 -- T.SendScores, CASE WHEN(O.Ordinal <= @DefaultAssessmentCount) THEN 1 ELSE 0 END AS SendScores
352 FROM @AssessmentTestIds T
353 JOIN (
354 SELECT ATI.AssessmentTestId,
355 ROW_NUMBER() OVER(ORDER BY ATI.ScoreCount DESC, ATI.AssessmentTestOrdinal) AS 'Ordinal'
356 FROM @AssessmentTestIds ATI
357 --ORDER
358 -- BY ATI.AssessmentTestOrdinal
359 ) O
360 ON T.AssessmentTestId = O.AssessmentTestId
361 END -------------------------------------------------
362 ---------------------------------------------
363 --SELECT 'line 341', T.AssessmentTestId, T.SendScores, T.ScoreCount, T.AssessmentTestOrdinal FROM @AssessmentTestIds T ORDER BY T.SendScores DESC, T.ScoreCount DESC,T.AssessmentTestOrdinal
364 ---------------------------------------------
365
366
367
368
369 ---------------------------------------------
370 -- Omit tests that have no scores
371 ---------------------------------------------
372 UPDATE AT
373 SET SendScores = 0
374 FROM @AssessmentTestIds AT
375 WHERE AT.ScoreCount = 0
376 ---------------------------------------------
377
378
379
380 ---------------------------------------------
381 -- Delete student Scores where Score.TestID not in Tests where SendScores=1
382 ---------------------------------------------
383 --SELECT COUNT(*) AS ScoreCount FROM @StudentAssessmentTestScores
384 ---------------------------------------------
385 DELETE @StudentAssessmentTestScores
386 WHERE AssessmentTestId NOT IN (
387 SELECT T.AssessmentTestId
388 FROM @AssessmentTestIds T
389 WHERE T.SendScores = 1
390 )
391 ---------------------------------------------
392 --SELECT COUNT(*) AS ScoreCount FROM @StudentAssessmentTestScores
393 ---------------------------------------------
394
395
396
397
398
399 ---------------------------------------------
400 -- StudentIds
401 ---------------------------------------------
402 DECLARE @StudentIds TABLE (
403 StudentId int,
404 Ordinal int,
405 -----------------------------------
406 PRIMARY KEY (StudentId)
407 )
408 ---------------------------------------------
409 INSERT @StudentIds(StudentId)
410 SELECT DISTINCT S.StudentId
411 FROM @StudentAssessmentTestScores S
412 ---------------------------------------------
413 --SELECT COUNT(*) AS 'StudentCount' FROM @StudentIds
414 ---------------------------------------------
415
416
417
418
419 ---------------------------------------------
420 -- Fetch Student-Count
421 ---------------------------------------------
422 DECLARE @StudentCount int
423 SELECT @StudentCount = COUNT(StudentId) FROM @StudentIds
424 ---------------------------------------------
425 --SELECT @StudentCount AS '@StudentCount'
426 ---------------------------------------------
427
428
429
430
431 ---------------------------------------------
432 -- If @Order-By-Assessment-Test-Id is passed in,
433 -- and we are sending scores for that test
434 -- assign student-ordinals accordingly, for ordering and paging
435 -- Gotcha: when ordering by score... must account for NULL scores
436 ---------------------------------------------
437 IF(EXISTS( SELECT 1
438 FROM @AssessmentTestIds AT
439 WHERE AT.AssessmentTestId = @OrderingAssessmentTestId
440 AND AT.SendScores = 1
441 ))
442 BEGIN ---------------------------------
443 DECLARE @StudentOrderingScores TABLE (
444 StudentId int,
445 Score decimal(18, 2),
446 -----------------------------------
447 PRIMARY KEY (StudentId)
448 )
449 ---------------------------------------------
450 INSERT @StudentOrderingScores(StudentId, Score)
451 SELECT SATS.StudentId, SATS.StudentAssessmentTestScore
452 FROM @StudentAssessmentTestScores SATS
453 WHERE SATS.AssessmentTestId = @OrderingAssessmentTestId
454 ---------------------------------------------
455 INSERT @StudentOrderingScores(StudentId, Score)
456 SELECT SI.StudentId, NULL
457 FROM @StudentIds SI
458 WHERE NOT EXISTS ( SELECT 1
459 FROM @StudentOrderingScores SOS
460 WHERE SOS.StudentId = SI.StudentId)
461 ---------------------------------------------
462 --SELECT * FROM @StudentOrderingScores
463 ---------------------------------------------
464 DECLARE @StudentScoreOrdinals TABLE (
465 StudentId int,
466 Score decimal(18, 2),
467 ScoreOrdinal int,
468 -----------------------------------
469 PRIMARY KEY (StudentId)
470 )
471 ---------------------------------------------
472 INSERT @StudentScoreOrdinals(
473 StudentId,
474 Score,
475 ScoreOrdinal)
476 SELECT SOS.StudentId,
477 SOS.Score,
478 ROW_NUMBER() OVER(ORDER BY
479 CASE WHEN(@OrderingDirection = 'DESC')
480 THEN @StudentCount - ISNULL(SOS.Score, 0)
481 ELSE ISNULL(SOS.Score, 99999) -- place NULLs at the end of ASC ordered scores
482 END,
483 ST.StudentId) AS NewOrdinal
484 FROM @StudentIds ST
485 JOIN @StudentOrderingScores SOS
486 ON ST.StudentId = SOS.StudentId
487 ---------------------------------------------
488 --SELECT 'before', S.StudentId, S.LastName, S.FirstName, S.Ordinal, O.Score, O.NewOrdinal FROM @Students S LEFT JOIN @NewStudentOrdinals O ON S.StudentId = O.StudentId ORDER BY S.Ordinal
489 ---------------------------------------------
490 UPDATE S
491 SET Ordinal = SSO.ScoreOrdinal
492 FROM @StudentIds S
493 JOIN @StudentScoreOrdinals SSO
494 ON S.StudentId = SSO.StudentId
495 ---------------------------------------------
496 --SELECT 'after', S.StudentId, S.LastName, S.FirstName, S.Ordinal, O.Score, O.NewOrdinal FROM @Students S LEFT JOIN @NewStudentOrdinals O ON S.StudentId = O.StudentId ORDER BY S.Ordinal
497 END ---------------------------------
498 ELSE
499 BEGIN ---------------------------------------------
500 DECLARE @StudentNameOrdinals TABLE (
501 StudentId int,
502 LastName varchar(100),
503 FirstName varchar(100),
504 NameOrdinal int,
505 -----------------------------------
506 PRIMARY KEY (StudentId)
507 )
508 ---------------------------------------------
509 INSERT @StudentNameOrdinals(
510 StudentId,
511 LastName,
512 FirstName,
513 NameOrdinal)
514
515 SELECT S.StudentId,
516 P.FirstName,
517 P.LastName,
518 ROW_NUMBER() OVER(ORDER BY P.LastName, P.FirstName, P.PersonId)
519 FROM @StudentIds S
520 JOIN dbo.DataViewModePersons(NULL,@DataViewMode) P
521 ON S.StudentId = P.PersonId
522 ---------------------------------------------
523 --SELECT * FROM @StudentNameOrdinals ORDER BY NameOrdinal
524 ---------------------------------------------
525 UPDATE S
526 SET Ordinal = CASE WHEN(@OrderingDirection = 'DESC')
527 THEN @StudentCount - SNO.NameOrdinal
528 ELSE SNO.NameOrdinal
529 END
530 FROM @StudentIds S
531 JOIN @StudentNameOrdinals SNO
532 ON S.StudentId = SNO.StudentId
533 ---------------------------------------------
534 --SELECT * FROM @StudentIds ORDER BY Ordinal
535 END ---------------------------------------------
536 ---------------------------------------------
537
538
539
540
541 ---------------------------------------------
542 -- Delete Students not on current Page
543 ---------------------------------------------
544 DECLARE @FirstStudentIndex int = ((@StudentPageIndex - 1) * @StudentPageSize) + 1
545 DECLARE @LastStudentIndex int = @StudentPageIndex * @StudentPageSize
546 --SELECT @FirstStudentIndex AS '@FirstStudentIndex', @LastStudentIndex AS '@LastStudentIndex'
547 ---------------------------------------------
548 DELETE @StudentIds
549 WHERE Ordinal < @FirstStudentIndex
550 OR Ordinal > @LastStudentIndex
551 ---------------------------------------------
552
553
554
555
556 ---------------------------------------------
557 -- Compose DataPages
558 ---------------------------------------------
559 DECLARE @DataPages TABLE
560 (
561 DataPageIndex int,
562 FirstRecordIndex int,
563 LastRecordIndex int,
564 DataPageRecordCount int
565 )
566 INSERT @DataPages(DataPageIndex, FirstRecordIndex, LastRecordIndex, DataPageRecordCount)
567 SELECT DP.DataPageIndex, FirstRecordIndex, LastRecordIndex, DataPageRecordCount
568 FROM dbo.DataPages(@StudentPageSize, @StudentCount) DP
569 ---------------------------------------------
570 --SELECT * FROM @DataPages
571 ---------------------------------------------
572
573
574
575
576 ---------------------------------------------
577 -- Delete Student Scores for students not on current Page
578 ---------------------------------------------
579 DELETE @StudentAssessmentTestScores
580 WHERE StudentId NOT IN (
581 SELECT S.StudentId
582 FROM @StudentIds S
583 )
584 ---------------------------------------------
585
586
587
588
589 ---------------------------------------------
590 -- Student CROSS JOIN Tests
591 ---------------------------------------------
592 DECLARE @StudentsAndTests TABLE (
593 StudentId int,
594 AssessmentTestId int,
595 PossiblyEncryptedAssessmentTestId varchar(500),
596 AssessmentTestOrdinalByGroup int,
597 -----------------------------------
598 PRIMARY KEY (StudentId, AssessmentTestId)
599 )
600 ---------------------------------------------
601 INSERT @StudentsAndTests
602 SELECT S.StudentId,
603 T.AssessmentTestId,
604 CASE @EncryptIds
605 WHEN 1 THEN T.PossiblyEncryptedAssessmentTestId
606 ELSE CAST(T.AssessmentTestId as varchar(200))
607 END,
608 T.AssessmentTestOrdinal
609 FROM @StudentIds S
610 CROSS
611 JOIN @AssessmentTestIds T
612 WHERE T.SendScores = 1
613 ---------------------------------------------
614 --SELECT * FROM @StudentsAndTests ORDER BY StudentId, AssessmentTestOrdinalByGroup
615 ---------------------------------------------
616
617
618
619
620
621 ---------------------------------------------
622 -- Student Assessment Test Scores with blanks for un-taken tests
623 ---------------------------------------------
624 DECLARE @StudentAssessmentTestScoresWithBlanks TABLE (
625 StudentId int,
626 AssessmentTestId int,
627 PossiblyEncryptedAssessmentTestId varchar(500),
628 AssessmentTestOrdinalByGroup int,
629 StudentAssessmentTestScore money,
630 AssessmentTestPerformanceLevelId int,
631 AssessmentTestPerformanceLevel varchar(100),
632 AssessmentTestPerformanceLevelColor varchar(6),
633 IsPassFail bit,
634 Passed bit,
635 -----------------------------------
636 PRIMARY KEY (StudentId, AssessmentTestId)
637 )
638 ---------------------------------------------
639 INSERT @StudentAssessmentTestScoresWithBlanks(
640 StudentId,
641 AssessmentTestId,
642 PossiblyEncryptedAssessmentTestId,
643 AssessmentTestOrdinalByGroup,
644 StudentAssessmentTestScore,
645 AssessmentTestPerformanceLevelId,
646 AssessmentTestPerformanceLevel,
647 AssessmentTestPerformanceLevelColor,
648 IsPassFail,
649 Passed)
650 SELECT SAT.StudentId,
651 SAT.AssessmentTestId,
652 SAT.PossiblyEncryptedAssessmentTestId,
653 SAT.AssessmentTestOrdinalByGroup,
654 SATS.StudentAssessmentTestScore,
655 SATS.AssessmentTestPerformanceLevelId,
656 SATS.AssessmentTestPerformanceLevel,
657 SATS.AssessmentTestPerformanceLevelColor,
658 SATS.IsPassFail,
659 SATS.Passed
660 FROM @StudentsAndTests SAT
661 LEFT
662 JOIN @StudentAssessmentTestScores SATS
663 ON SAT.StudentId = SATS.StudentId
664 AND SAT.AssessmentTestId = SATS.AssessmentTestId
665 ---------------------------------------------
666
667
668
669
670
671 ---------------------------------------------
672 -- Assessment Test Performance Level Score Counts
673 ---------------------------------------------
674 DECLARE @AssessmentTestPerformanceLevelScoreCounts TABLE (
675 AssessmentTestPerformanceLevelId int,
676 ScoreCount int ,
677 -----------------------------------
678 PRIMARY KEY (AssessmentTestPerformanceLevelId)
679 )
680 ---------------------------------------------
681 INSERT @AssessmentTestPerformanceLevelScoreCounts
682 SELECT SATS.AssessmentTestPerformanceLevelId,
683 COUNT(SATS.StudentId)
684 FROM @StudentAssessmentTestScores SATS
685 WHERE SATS.AssessmentTestPerformanceLevelId IS NOT NULL
686 GROUP
687 BY SATS.AssessmentTestPerformanceLevelId
688 ---------------------------------------------
689 --SELECT * FROM @AssessmentTestPerformanceLevelScoreCounts
690 ---------------------------------------------
691
692
693
694
695
696
697
698
699
700 ---------------------------------------------
701 -- Students
702 ---------------------------------------------
703 DECLARE @Students TABLE (
704 StudentId int,
705 FirstName varchar(100),
706 LastName varchar(100),
707 DisplayName varchar(300),
708 Ordinal int,
709 PossiblyEncryptedStudentId varchar(500),
710 -----------------------------------
711 PRIMARY KEY (StudentId)
712 )
713 ---------------------------------------------
714 INSERT @Students(
715 StudentId,
716 FirstName,
717 LastName,
718 DisplayName,
719 Ordinal,
720 PossiblyEncryptedStudentId)
721 SELECT P.PersonId,
722 P.FirstName,
723 P.LastName,
724 P.FirstName + ' ' + P.LastName,
725 S.Ordinal,
726 CASE @EncryptIds
727 WHEN 1 THEN StudentEncryptedIds.EncryptedId
728 ELSE CAST(P.PersonId as varchar(200))
729 END
730 FROM @StudentIds S
731 JOIN dbo.DataViewModePersons(NULL,@DataViewMode) P
732 ON S.StudentId = P.PersonId
733 JOIN EncryptedIds StudentEncryptedIds
734 ON S.StudentId = StudentEncryptedIds.Id
735 ---------------------------------------------
736 --SELECT * FROM @Students
737 ---------------------------------------------
738
739
740
741
742
743
744 ---------------------------------------------
745 --
746 ---------------------------------------------
747 DECLARE @AssessmentTestPerformanceLevels TABLE
748 ( -------------------------------------------
749 AssessmentTestPerformanceLevelId int,
750 PossiblyEncryptedAssessmentTestPerformanceLevelId varchar(500),
751 AssessmentTestPerformanceLevel varchar(50),
752 AssessmentTestPerformanceLevelMinimumScore int,
753 AssessmentTestPerformanceLevelMaximumScore int,
754 AssessmentTestPerformanceLevelColor varchar(20),
755 AssessmentTestPerformanceLevelOrdinalByTest int,
756 AssessmentTestPerformanceLevelMeetsStandard bit,
757 AssessmentTestId int,
758 HighestPossibleScore int,
759 ScoreCount int
760 --IsAlternate bit
761 -------------------------------------------
762 PRIMARY KEY (AssessmentTestPerformanceLevelId)
763 )
764 ---------------------------------------------
765 INSERT @AssessmentTestPerformanceLevels (
766 AssessmentTestPerformanceLevelId,
767 PossiblyEncryptedAssessmentTestPerformanceLevelId,
768 AssessmentTestPerformanceLevel,
769 AssessmentTestPerformanceLevelMinimumScore,
770 AssessmentTestPerformanceLevelMaximumScore,
771 AssessmentTestPerformanceLevelColor,
772 AssessmentTestPerformanceLevelOrdinalByTest,
773 AssessmentTestPerformanceLevelMeetsStandard,
774 AssessmentTestId,
775 HighestPossibleScore,
776 ScoreCount
777 -- IsAlternate
778 ) -------------------------------------------
779 SELECT PL.AssessmentTestPerformanceLevelId,
780 CASE @EncryptIds
781 WHEN 1 THEN EPL.EncryptedId
782 ELSE CAST(PL.AssessmentTestPerformanceLevelId as varchar(200))
783 END,
784 AssessmentTestPerformanceLevel,
785 AssessmentTestPerformanceLevelMinimumScore,
786 AssessmentTestPerformanceLevelMaximumScore,
787 AssessmentTestPerformanceLevelColor,
788 AssessmentTestPerformanceLevelOrdinalByTest,
789 CASE
790 WHEN(PL.AssessmentTestPerformanceLevelMinimumScore >= AT.MinimumPassingScore)
791 THEN CAST(1 AS bit)
792 ELSE CAST(0 AS bit)
793 END, --PL.AssessmentTestPerformanceLevelMeetsStandard
794 ATI.AssessmentTestId,
795 AT.HighestPossibleScore,
796 ISNULL(ATPLSC.ScoreCount, 0)
797 -- CASE WHEN(AT.UseAsAlternateForAssessmentTestId IS NOT NULL) THEN 1 ELSE 0 END AS 'IsAlternate'
798 FROM @AssessmentTestIds ATI
799 JOIN AssessmentTests AT
800 ON AT.AssessmentTestId = ATI.AssessmentTestId
801 --ON ISNULL(AT.UseAsAlternateForAssessmentTestId, AT.AssessmentTestId) = ATI.AssessmentTestId
802 AND ATI.SendScores = 1
803 JOIN AssessmentTestPerformanceLevels PL
804 ON AT.AssessmentTestId = PL.AssessmentTestId
805 JOIN EncryptedIds EPL
806 ON PL.AssessmentTestPerformanceLevelId = EPL.Id
807 LEFT
808 JOIN @AssessmentTestPerformanceLevelScoreCounts ATPLSC
809 ON ATPLSC.AssessmentTestPerformanceLevelId
810 = PL.AssessmentTestPerformanceLevelId
811 WHERE ATPLSC.ScoreCount > 0
812 --OR AT.UseAsAlternateForAssessmentTestId IS NULL
813 ---------------------------------------------
814 --SELECT 'Line 678', * FROM @AssessmentTestPerformanceLevels
815 ---------------------------------------------
816
817
818
819
820 ---------------------------------------------
821 --
822 ---------------------------------------------
823 DECLARE @AssessmentTests TABLE
824 ( -------------------------------------------
825 AssessmentTestId int,
826 PossiblyEncryptedAssessmentTestId varchar(500),
827 AssessmentTestLabel varchar(200),
828 ScoreCount int,
829 SendScores bit,
830 AssessmentTestIsPassFail bit,
831 HighestPossibleScore int,
832 LowestPossibleScore int,
833 MinimumPassingScore int,
834 AssessmentTestOrdinalByGroup int,
835 CorrespondingAssessmentTestGroupId int,
836 EncryptedCorrespondingAssessmentTestGroupId varchar(500),
837 CorrespondingAssessmentTestGroup varchar(200),
838 -------------------------------------------
839 PRIMARY KEY (AssessmentTestId)
840 )
841 ---------------------------------------------
842 INSERT @AssessmentTests (
843 AssessmentTestId,
844 PossiblyEncryptedAssessmentTestId,
845 AssessmentTestLabel,
846 ScoreCount,
847 SendScores,
848 AssessmentTestIsPassFail,
849 HighestPossibleScore,
850 LowestPossibleScore,
851 MinimumPassingScore,
852 AssessmentTestOrdinalByGroup,
853 CorrespondingAssessmentTestGroupId,
854 EncryptedCorrespondingAssessmentTestGroupId,
855 CorrespondingAssessmentTestGroup)
856 SELECT AT.AssessmentTestId,
857 CASE @EncryptIds
858 WHEN 1 THEN ATI.POssiblyEncryptedAssessmentTestId
859 ELSE CAST(AT.AssessmentTestId as varchar(200))
860 END,
861 AssessmentTestLabel,
862 ATI.ScoreCount,
863 ATI.SendScores,
864 AssessmentTestIsPassFail,
865 HighestPossibleScore,
866 LowestPossibleScore,
867 MinimumPassingScore,
868 ATI.AssessmentTestOrdinal,
869 ATG.AssessmentTestGroupId,
870 AssessmentTestGroupEncryptedIds.EncryptedId,
871 ATG.AssessmentTestGroup -- AS 'CorrespondingAssessmentTestGroup',
872 FROM @AssessmentTestIds ATI
873 JOIN AssessmentTests AT
874 ON ATI.AssessmentTestId = AT.AssessmentTestId
875 LEFT
876 JOIN AssessmentTestGroups ATG
877 ON AT.CorrespondingAssessmentTestGroupId = ATG.AssessmentTestGroupId
878 AND AT.CorrespondingAssessmentTestGroupId <> @AssessmentTestGroupId
879 LEFT
880 JOIN EncryptedIds AssessmentTestGroupEncryptedIds
881 ON ATG.AssessmentTestGroupId = AssessmentTestGroupEncryptedIds.Id
882 ---------------------------------------------
883 --SELECT 'Line 842', * FROM @AssessmentTests ORDER BY SendScores DESC, AssessmentTestOrdinalByGroup
884 ---------------------------------------------
885
886
887 ---------------------------------------------
888 --
889 ---------------------------------------------
890 DECLARE @AssessmentTestGroups TABLE
891 ( -------------------------------------------
892 AssessmentTestGroupId int,
893 PossiblyEncryptedAssessmentTestGroupId varchar(500),
894 AssessmentTestGroup varchar(500)
895 -------------------------------------------
896 PRIMARY KEY (AssessmentTestGroupId)
897 )
898 ---------------------------------------------
899 INSERT @AssessmentTestGroups (
900 AssessmentTestGroupId,
901 PossiblyEncryptedAssessmentTestGroupId,
902 AssessmentTestGroup)
903 SELECT ATG.AssessmentTestGroupId,
904 CASE @EncryptIds
905 WHEN 1 THEN EID.EncryptedId
906 ELSE CAST(ATG.AssessmentTestGroupId as varchar(200))
907 END,
908 ATG.AssessmentTestGroup
909 FROM AssessmentTestGroups ATG
910 JOIN EncryptedIds EID
911 ON ATG.AssessmentTestGroupId = EID.Id
912 WHERE ATG.AssessmentTestGroupId = @AssessmentTestGroupId
913 ---------------------------------------------
914
915
916
917 ---------------------------------------------
918 -- Assessment-Test-Groups / Assessment-Tests / Performance-Levels
919 ---------------------------------------------
920 DECLARE @AssessmentTestGroup Xml -- varchar(max)
921 ---------------------------------------------
922 SET @AssessmentTestGroup = (
923 SELECT AssessmentTestGroup.PossiblyEncryptedAssessmentTestGroupId AS 'AssessmentTestGroupId',
924 AssessmentTestGroup.AssessmentTestGroup,
925 --AssessmentTestGroup.AssessmentTestGroupIsAutomatic,
926 --AssessmentTestGroup.AssessmentTestSubjectAreaId,
927 --AssessmentTestGroup.AssessmentTestSubjectArea,
928 ---------------------------------------
929 AssessmentTest.PossiblyEncryptedAssessmentTestId AS 'Id',
930 AssessmentTest.AssessmentTestLabel AS 'Label',
931 AssessmentTest.ScoreCount AS 'ScoreCount',
932 dbo.JavascriptBoolean(AssessmentTest.AssessmentTestIsPassFail) AS 'IsPassFail',
933 AssessmentTest.HighestPossibleScore,
934 AssessmentTest.LowestPossibleScore,
935 AssessmentTest.MinimumPassingScore,
936 AssessmentTest.AssessmentTestOrdinalByGroup AS 'Ordinal',
937 AssessmentTest.CorrespondingAssessmentTestGroupId AS 'GroupId',
938 ---------------------------------------
939 AssessmentTestPerformanceLevel.PossiblyEncryptedAssessmentTestPerformanceLevelId AS 'Id',
940 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevel AS 'PerformanceLevel',
941 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelColor AS 'Color',
942 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelMaximumScore AS 'MaximumScore',
943 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelMinimumScore AS 'MinimumScore',
944 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelOrdinalByTest AS 'Ordinal'
945 FROM @AssessmentTestGroups AssessmentTestGroup
946 CROSS
947 JOIN @AssessmentTests AssessmentTest
948 LEFT
949 JOIN @AssessmentTestPerformanceLevels AssessmentTestPerformanceLevel
950 ON AssessmentTest.AssessmentTestId = AssessmentTestPerformanceLevel.AssessmentTestId
951 WHERE AssessmentTest.SendScores = 1
952 --AND AssessmentTest.ScoreCount > 0
953 ORDER
954 BY AssessmentTest.AssessmentTestOrdinalByGroup,
955 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevelOrdinalByTest,
956 AssessmentTestPerformanceLevel.AssessmentTestPerformanceLevel
957 FOR XML AUTO, ROOT('AssessmentTestGroups')
958 )
959 ---------------------------------------------
960 --SELECT @AssessmentTestGroup AS '@AssessmentTestGroup'
961 ---------------------------------------------
962
963
964
965
966
967 ---------------------------------------------
968 -- Assessment-Tests
969 ---------------------------------------------
970 DECLARE @AssessmentTestsXml Xml -- varchar(max)
971 ---------------------------------------------
972 SET @AssessmentTestsXml = (
973 SELECT AssessmentTest.PossiblyEncryptedAssessmentTestId AS 'Id',
974 AssessmentTest.AssessmentTestLabel AS 'Label',
975 AssessmentTest.ScoreCount AS 'ScoreCount',
976 dbo.JavascriptBoolean(AssessmentTest.SendScores) AS 'Active',
977 --AssessmentTest.AssessmentTestIsPassFail AS 'IsPassFail',
978 --AssessmentTest.HighestPossibleScore,
979 --AssessmentTest.LowestPossibleScore,
980 --AssessmentTest.MinimumPassingScore,
981 AssessmentTest.AssessmentTestOrdinalByGroup AS 'Ordinal'
982
983 FROM @AssessmentTests AssessmentTest
984 WHERE AssessmentTest.SendScores = 1 OR AssessmentTest.ScoreCount > 0
985 ORDER
986 BY AssessmentTest.AssessmentTestOrdinalByGroup
987 FOR XML AUTO, ROOT('AssessmentTests')
988 )
989 ---------------------------------------------
990 --SELECT @AssessmentTestsXml AS '@AssessmentTestsXml'
991 ---------------------------------------------
992
993
994
995
996 ---------------------------------------------
997 -- StudentAssessmentTestScores-Levels
998 ---------------------------------------------
999 DECLARE @StudentsWithAssessmentTestScoresXml Xml -- varchar(max)
1000 ---------------------------------------------
1001 SET @StudentsWithAssessmentTestScoresXml = (
1002 SELECT Student.PossiblyEncryptedStudentId AS 'StudentId',
1003 Student.FirstName,
1004 Student.LastName,
1005 Student.Ordinal,
1006 @StudentPageIndex AS 'PageIndex',
1007 ----------------
1008 StudentAssessmentTestScore.PossiblyEncryptedAssessmentTestId AS 'Id',
1009 StudentAssessmentTestScore.AssessmentTestOrdinalByGroup AS 'Ordinal',
1010 dbo.TrimZeros(StudentAssessmentTestScore.StudentAssessmentTestScore) AS 'Score',
1011 StudentAssessmentTestScore.Passed AS 'Passed',
1012 -- dbo.JavascriptBoolean(StudentAssessmentTestScore.IsPassFail) AS 'IsPassFail',
1013 StudentAssessmentTestScore.IsPassFail AS 'IsPassFail',
1014 StudentAssessmentTestScore.AssessmentTestPerformanceLevel AS 'PerformanceLevel',
1015 StudentAssessmentTestScore.AssessmentTestPerformanceLevelId AS 'PerformanceLevelId',
1016 StudentAssessmentTestScore.AssessmentTestPerformanceLevelColor AS 'Color'
1017 ------------------
1018 FROM @Students Student
1019 JOIN @StudentAssessmentTestScoresWithBlanks StudentAssessmentTestScore
1020 ON Student.StudentId = StudentAssessmentTestScore.StudentId
1021 ORDER
1022 BY Student.Ordinal,
1023 StudentAssessmentTestScore.AssessmentTestOrdinalByGroup
1024 FOR XML AUTO,ROOT('StudentsWithAssessmentTestScoresXml')
1025 )
1026 ---------------------------------------------
1027 --SELECT @StudentsWithAssessmentTestScoresXml AS '@StudentsWithAssessmentTestScoresXml'
1028 ---------------------------------------------
1029
1030
1031
1032 /*---------------------------------------------
1033 -- Students
1034 ---------------------------------------------
1035 DECLARE @StudentsXml Xml -- varchar(max)
1036 ---------------------------------------------
1037 SET @StudentsXml = (
1038 SELECT Student.PossiblyEncryptedStudentId AS 'StudentId',
1039 Student.FirstName,
1040 Student.LastName,
1041 Student.Ordinal,
1042 @StudentPageIndex AS 'PageIndex'
1043 FROM @Students Student
1044 ORDER
1045 BY Student.Ordinal
1046 FOR XML AUTO,ROOT('Students')
1047 )
1048 ---------------------------------------------
1049 --SELECT @StudentsXml AS '@StudentsXml'
1050 ---------------------------------------------*/
1051
1052
1053
1054
1055
1056 ---------------------------------------------
1057 DECLARE @StudentPages Xml
1058 ---------------------------------------------
1059 SET @StudentPages = (
1060 SELECT DataPageIndex AS '@PageIndex',
1061 FirstRecordIndex AS '@FirstRecordIndex',
1062 LastRecordIndex AS '@LastRecordIndex',
1063 DataPageRecordCount AS '@PageRecordCount'
1064 FROM @DataPages
1065 FOR XML PATH('StudentPage'), ROOT('StudentPages')
1066 )
1067 ---------------------------------------------
1068 --SELECT @StudentPages AS '@StudentPages'
1069 ---------------------------------------------
1070
1071
1072
1073
1074 /*---------------------------------------------
1075 -- StudentAssessmentTestScores-Levels
1076 ---------------------------------------------
1077 DECLARE @AssessmentTestScoresXml Xml -- varchar(max)
1078 ---------------------------------------------
1079 SET @AssessmentTestScoresXml = (
1080 SELECT Student.Ordinal AS '@Ordinal',
1081 StudentAssessmentTestScore.PossiblyEncryptedAssessmentTestId AS '@Id',
1082 --StudentAssessmentTestScore.AssessmentTestOrdinalByGroup AS '@Ordinal',
1083 dbo.TrimZeros(StudentAssessmentTestScore.StudentAssessmentTestScore) AS '@Score',
1084 -- StudentAssessmentTestScore.Passed AS 'Passed',
1085 StudentAssessmentTestScore.IsPassFail AS '@IsPassFail',
1086 StudentAssessmentTestScore.AssessmentTestPerformanceLevel AS '@PerformanceLevel',
1087 StudentAssessmentTestScore.AssessmentTestPerformanceLevelId AS '@PerformanceLevelId',
1088 StudentAssessmentTestScore.AssessmentTestPerformanceLevelColor AS '@Color'
1089 ------------------
1090 FROM @Students Student
1091 JOIN @StudentAssessmentTestScoresWithBlanks StudentAssessmentTestScore
1092 ON Student.StudentId = StudentAssessmentTestScore.StudentId
1093 ORDER
1094 BY StudentAssessmentTestScore.AssessmentTestOrdinalByGroup,
1095 Student.Ordinal
1096 FOR XML PATH('StudentAssessmentTestScore'),ROOT('StudentAssessmentTestScores')
1097 )
1098 ---------------------------------------------
1099 --SELECT @AssessmentTestScoresXml AS '@AssessmentTestScoresXml'
1100 */---------------------------------------------
1101
1102
1103
1104 ---------------------------------------------
1105 -- StudentGroup
1106 ---------------------------------------------
1107 DECLARE @StudentGroup Xml -- varchar(max)
1108 ---------------------------------------------
1109 SET @StudentGroup = (
1110 SELECT StudentGroup.StudentGroupLabel
1111 FROM StudentGroups StudentGroup
1112 WHERE StudentGroup.StudentGroupId = @StudentGroupId
1113 FOR XML AUTO, ROOT('StudentGroups')
1114 )
1115 ---------------------------------------------
1116 --SELECT @StudentGroup AS '@StudentGroup'
1117 ---------------------------------------------
1118
1119
1120
1121 ---------------------------------------------
1122 -- Assemble output Xml
1123 ---------------------------------------------
1124 SET @AssessmentTestGroup = dbo.XmlNormalize(@AssessmentTestGroup)
1125 SET @Xml = dbo.XmlAdopt(@Xml,@AssessmentTestGroup,'Xml','AssessmentTestGroup')
1126 SET @StudentPages = dbo.XmlJoin(@StudentPages, @StudentsWithAssessmentTestScoresXml, 'StudentPage.PageIndex=Student.PageIndex')
1127 SET @Xml = dbo.XmlAdopt(@Xml,@StudentPages,'Xml','StudentPages')
1128 SET @Xml = dbo.XmlAdopt(@Xml,@AssessmentTestsXml,'Xml','AssessmentTests')
1129 SET @Xml = dbo.XmlAdopt(@Xml,@StudentGroup,'Xml','StudentGroup')
1130 ---------------------------------------------
1131 --SET @Xml = dbo.XmlAdopt(@Xml,@StudentAssessmentTestScoresXml,'Xml','StudentAssessmentTestScores')
1132 --SET @Xml = dbo.XmlJoin(@Xml,@AssessmentTestScoresXml,'AssessmentTest.Id=StudentAssessmentTestScore.Id')
1133 ---------------------------------------------
1134
1135
1136
1137
1138 ---------------------------------------------
1139 -- Track Assembly time
1140 ---------------------------------------------
1141 DECLARE @XmlAssemblyTimeInMilliseconds int = DATEDIFF(ms, @StartTime, CAST(GETDATE() as time))
1142 SET @Xml = dbo.XmlAssignAttributeValues(@Xml, '//Xml', 'xmlAssemblyTimeInMilliseconds', @XmlAssemblyTimeInMilliseconds)
1143 ---------------------------------------------
1144
1145
1146-- SELECT @Xml AS '@Xml'
1147 ---------------------------------------------
1148 RETURN @Xml
1149END