我有一些可以遍历QABLOCK并打印它们的SQL代码（在生产中，PRINT将更改为INSERT）。
-- XML instance DECLARE @x1 XML SELECT @x1 = ' <tests> <test id="1"> <qablock number="1"> <question>What is 1 + 1?</question> <explanation>It"s 2.</explanation> <options> <option number="1" value="1" correct="0" /> <option number="2" value="2" correct="1" /> <option number="3" value="3" correct="0" /> <option number="4" value="4" correct="0" /> <option number="5" value="5" correct="0" /> </options> </qablock> <qablock number="2"> <question>What is 2 + 2?</question> <explanation>It"s 4.</explanation> <options> <option number="1" value="1" correct="0" /> <option number="2" value="2" correct="0" /> <option number="3" value="3" correct="0" /> <option number="4" value="4" correct="1" /> <option number="5" value="5" correct="0" /> </options> </qablock> </test> </tests> ' DECLARE @cnt INT, @totCnt INT, @child XML -- counter variables SELECT @cnt = 1, @totCnt = @x1.value('count(/tests/test/qablock)','INT') -- loop WHILE @cnt <= @totCnt BEGIN SELECT 1 AS tests_id, --this is hard-coded @cnt AS qablock_number, @x1.value('(/tests/test/qablock[position()=sql:variable("@cnt")]/question/text())[1]','varchar(500)') AS question, @x1.value('(/tests/test/qablock[position()=sql:variable("@cnt")]/explanation/text())[1]','varchar(1000)') AS explanation PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR) PRINT 'Child element: ' + CAST(@child AS VARCHAR(max)) PRINT '' -- increment the counter variable SELECT @cnt = @cnt + 1 END
我不知道这是否是正确的程序，但至少它似乎有效。我接下来要做的是运行另一个类似的脚本，该脚本循环遍历所有OPTIONS并打印它们。对于每个OPTION，我需要打印：QABLOCK号，OPTION号，OPTION值和OPTION is_correct。我什至无法接近。:(这是到目前为止我得到的：
-- XML instance DECLARE @x1 XML SELECT @x1 = ' <tests> <test id="1"> <qablock number="1"> <question>What is 1 + 1?</question> <explanation>It"s 2.</explanation> <options> <option number="1" value="1" correct="0" /> <option number="2" value="2" correct="1" /> <option number="3" value="3" correct="0" /> <option number="4" value="4" correct="0" /> <option number="5" value="5" correct="0" /> </options> </qablock> <qablock number="2"> <question>What is 2 + 2?</question> <explanation>It"s 4.</explanation> <options> <option number="1" value="1" correct="0" /> <option number="2" value="2" correct="0" /> <option number="3" value="3" correct="0" /> <option number="4" value="4" correct="1" /> <option number="5" value="5" correct="0" /> </options> </qablock> </test> </tests> ' DECLARE @cnt INT, @totCnt INT, @child XML -- counter variables SELECT @cnt = 1, @totCnt = @x1.value('count(/tests/test/qablock/options/option)','INT') -- loop WHILE @cnt <= @totCnt BEGIN SELECT 1 AS tests_id, --hard-coded value @x1.value('(/tests/test/qablock/@number)[1]','varchar(500)') AS qablock_number, @x1.value('(/tests/test/qablock/options/option[position()=sql:variable("@cnt")]/@number)[1]','varchar(500)') AS option_number PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR) PRINT 'Child element: ' + CAST(@child AS VARCHAR(max)) PRINT '' -- increment the counter variable SELECT @cnt = @cnt + 1 END
因此，很明显它搞砸了，但是我似乎无法弄清楚该更改什么。
首先，我不知道如何获取当前OPTION的父QABLOCK号。
其次，只有我的前5个option_number正确显示了其编号，后5个显示了NULL。
这应该可以解决问题：
-- XML instance DECLARE @x1 XML SELECT @x1 = ' <tests> <test id="1"> <qablock number="1"> <question>What is 1 + 1?</question> <explanation>It"s 2.</explanation> <options> <option number="1" value="1" correct="0" /> <option number="2" value="2" correct="1" /> <option number="3" value="3" correct="0" /> <option number="4" value="4" correct="0" /> <option number="5" value="5" correct="0" /> </options> </qablock> <qablock number="2"> <question>What is 2 + 2?</question> <explanation>It"s 4.</explanation> <options> <option number="1" value="1" correct="0" /> <option number="2" value="2" correct="0" /> <option number="3" value="3" correct="0" /> <option number="4" value="4" correct="1" /> <option number="5" value="5" correct="0" /> </options> </qablock> </test> </tests> ' declare @testCount int , @testIndex int , @qaCount int , @qaIndex int , @optCount int , @optIndex int , @testId int , @qaNo int , @question nvarchar(500) , @explanation nvarchar(500) , @optNo int , @optVal int , @optCorrect bit --for each test set @testCount = @x1.value('count(/tests/test)','int') set @testIndex = 1 while @testIndex <= @testCount begin --insert a record for this test set @testId = @x1.value('(/tests/test[sql:variable(''@testIndex'')]/@id)[1]','int') print 'insert into tests (id, ...) values (' + + cast(@testId as nvarchar(9)) + ')' print 'set @testIdDB = scope_identity()' --if we're using db generated ids rather than relying on the ids in the imported xml --for each qablock set @qaCount = @x1.value('count(/tests/test[sql:variable(''@testIndex'')]/qablock)','int') set @qaIndex = 1 while @qaIndex <= @qaCount begin --insert a qa record select @qaNo = @x1.value('(/tests/test[sql:variable(''@testIndex'')]/qablock[sql:variable(''@qaIndex'')]/@number)[1]','int') , @question = @x1.value('(/tests/test[sql:variable(''@testIndex'')]/qablock[sql:variable(''@qaIndex'')]/question/text())[1]','nvarchar(500)') , @explanation = @x1.value('(/tests/test[sql:variable(''@testIndex'')]/qablock[sql:variable(''@qaIndex'')]/explanation/text())[1]','nvarchar(500)') print 'insert into qa (testId, qaNo, Question, Explanation) values (@testIdDB, ' + cast(@qaNo as nvarchar(9)) + ', ' + quotename(@question,'''') + ', ' + quotename(@explanation,'''') + ')' print 'set @qaIdDB = scope_identity()' --for each option set @optCount = @x1.value('count(/tests/test[sql:variable(''@testIndex'')]/qablock[sql:variable(''@qaIndex'')]/options/option)','int') set @optIndex = 1 while @optIndex <= @optCount begin --insert an option record select @optNo = @x1.value('(/tests/test[sql:variable(''@testIndex'')]/qablock[sql:variable(''@qaIndex'')]/options/option[sql:variable(''@optIndex'')]/@number)[1]','int') , @optVal = @x1.value('(/tests/test[sql:variable(''@testIndex'')]/qablock[sql:variable(''@qaIndex'')]/options/option[sql:variable(''@optIndex'')]/@value)[1]','int') , @optCorrect = @x1.value('(/tests/test[sql:variable(''@testIndex'')]/qablock[sql:variable(''@qaIndex'')]/options/option[sql:variable(''@optIndex'')]/@correct)[1]','bit') print 'insert into tests (qaId, number, value, isCorrect) values (@qaIdDB, ' + cast(@optNo as nvarchar(9)) + ', ' + cast(@optVal as nvarchar(9)) + ', ' + cast(@optCorrect as nchar(1)) + ')' set @optIndex = @optIndex + 1 end set @qaIndex = @qaIndex + 1 end set @testIndex = @testIndex + 1 end
还是更好（由于您提供的提示我的链接）：
select x.value('@id','int') testId FROM @x1.nodes('/tests/test') d(x) select x.value('../@id','int') parentTestId , x.value('@number','int') qaNumber , x.value('(question/text())[1]','nvarchar(500)') question , x.value('(explanation/text())[1]','nvarchar(500)') explanation FROM @x1.nodes('/tests/test/qablock') d(x) select x.value('../../@number','int') parentQaNumber , x.value('@number','int') number , x.value('@value','int') value , x.value('@correct','bit') correct FROM @x1.nodes('/tests/test/qablock/options/option') d(x)
