解析/处理 XML 并插入 SQL 表 [英] Parsing/Processing XML and Inserting Into SQL Tables
本文介绍了解析/处理 XML 并插入 SQL 表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一些 SQL 代码可以循环访问 QABLOCK 并打印它们(PRINT 将在生产中更改为 INSERT).
I've got some SQL code working that loops through the QABLOCKs and prints them (the PRINTs will change to INSERTs in production).
-- 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
我不知道这是否是正确的程序，但至少它似乎有效.接下来我想做的是运行另一个类似的脚本，循环遍历所有选项并打印它们.对于每个 OPTION，我需要打印:QABLOCK 编号、OPTION 编号、OPTION 值和 OPTION is_correct.我什至无法靠近.:(这是我到目前为止所得到的:
I don't know if that's the correct procedure whatsoever, but at least it seems to work. What I'd like to do next is run another similar script that loops through all the OPTIONs and PRINTs them as well. For each OPTION, I need to print: QABLOCK number, OPTION number, OPTION value, and OPTION is_correct. I can't even get close. :( Here's what I've got so far:
-- 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
所以，显然它搞砸了，但我似乎无法弄清楚要改变什么.
So, obviously it's messed up, but I can't seem to figure out what to change.
首先，我不知道如何获取当前OPTION的父级QABLOCK编号.
First of all, I don't know how to get the parent QABLOCK number of the current OPTION.
第二，只有我的前 5 个 option_number 正确显示了它们的数字，后 5 个显示为 NULL.
Second, only my first 5 option_number's are displaying their numbers properly, the second 5 are showing 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
或者更好(感谢您提供的链接提示我):
Or better still (thanks to the link you provided for prompting me):
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)
这篇关于解析/处理 XML 并插入 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
