Get questions of a specific question type (e.g. multiple choice) from a specific test in Blackboard Learn (bb_bb60) using the url to get the assessment pk1.

get_questions_of_type_and_their_answers_from_assessment.sql

use bb_bb60

declare @test_pk int
-- get the test_pk1 from the &content_id fragment of the url of the test in question, e.g.
 -- ~/webapps/assessment/take/launchAssessment.jsp?course_id=_1111_1&content_id=_7777777_1&mode=cpview
set @test_pk = 7777777

declare @question_type int
-- Choose the type of of question and answers that you wish to examine: 
-- 1=Multiple Choice, 2=True/False, 3=Multiple Anser, 4=Ordering, 5=Matching, 6=Fill In the Blank, 
-- 7=Essay Question, 8=Numeric, 9=Calculated, 10=FileUpload, 11=HotSpot, 12=FillintheBlankPlus, 13=Jumbled sentence, 
-- 14=Quiz Bowl, 15=LikeRT Scale, 16=Short Answer, 17=EitherOr
set @question_type = 3

-- qti_asi_data_for_7777777_qt_3
select CAST(data as varchar(max)), *  from bb_bb60.dbo.qti_asi_data
where bbmd_questiontype = @question_type 
and qti_asi_data.PARENT_PK1 =
	(select qad.PK1 from QTI_ASI_DATA qad 
	 where qad.PARENT_PK1 in 
		(select pk1 from QTI_ASI_DATA qad 
		 where qad.PK1 in 
			(select QTI_ASI_DATA_PK1 from GRADEBOOK_MAIN gm 
			 where gm.COURSE_CONTENTS_PK1 =  @test_pk) ) 
	);

-- qti_result_data_for_7777777_qt_3
select CAST(data as varchar(max)), *  from qti_result_data
where qti_asi_data_pk1 in (
	select pk1 from bb_bb60.dbo.qti_asi_data
	where bbmd_questiontype = @question_type 
	and qti_asi_data.PARENT_PK1 =
		(select qad.PK1 from QTI_ASI_DATA qad 
		 where qad.PARENT_PK1 in 
			(select pk1 from QTI_ASI_DATA qad 
			 where qad.PK1 in 
				(select QTI_ASI_DATA_PK1 from GRADEBOOK_MAIN gm 
				 where gm.COURSE_CONTENTS_PK1 =  @test_pk) ) )
	);