选择除结构化 XML 数据之外的行数据 [英] Select row data in addition to structured XML data
问题描述
我知道如何将 XML 变量连接到其他表，但在这种情况下，我试图从表中选择每一行以及 XML 的结构 从每个相应的表行，旁边那一行.我在网上找不到任何示例来帮助解决这个问题，因为大多数示例处理单个 XML 值(如果有的话，抱歉，我无法在无数其他 XML 示例中找到它们).
I know how to join an XML variable to other tables, but in this case, I am trying to select each row from a table plus the structure of the XML from each respective table row, alongside that row. I cannot find any examples online to help with this, as most examples deal with a single XML value (apologies if there are, I was unable to locate them in amongst the myriad of other XML examples).
表结构是这样的:
CREATE TABLE tbl_QuizHistory (
HistoryId int PRIMARY KEY,
QuizData xml NOT NULL
);
每个 QuizData 行值类似于:
<quizresult>
<question>
<questionText>Which fire extinguisher is most suitable for a waste paper basket fire?</questionText>
<answer number="0" value="0" chosen="0" imageURL="">Powder</answer>
<answer number="1" value="0" chosen="0" imageURL="">Carbon Dioxide (CO2)</answer>
<answer number="2" value="1" chosen="1" imageURL="">Water (H2O)</answer>
<answer number="3" value="0" chosen="0" imageURL="">Foam</answer>
<result>Correct</result>
</question>
<question>
<questionText>Should you use lifts during a fire?</questionText>
<answer number="0" value="0" chosen="0" imageURL="">Yes</answer>
<answer number="1" value="1" chosen="1" imageURL="">No</answer>
<result>Correct</result>
</question>
</quizresult>
在一个之前的问题中，我看到了如何分层显示 XML 数据 (@xml ==> questions ==> answer(s))，但仅适用于单个 XML 值，我将其调整为将问题/答案层次结构迁移到表中:
In an earlier question I was shown how to display the XML data hierarchically (@xml ==> questions ==> answer(s)), but only for a single XML value, which I adapted to migrate the question/answer hierarchy into a table:
-- Works for a single XML value/variable...
;WITH q AS (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID,
n.q.value('(./questionText)[1]', 'nvarchar(max)') AS questionText,
n.q.value('(./result)[1]', 'nvarchar(50)') AS result,
n.q.query('answer') AS answers
FROM
@xml.nodes('/quizresult/question') AS n (q)
),
qa AS (
SELECT
qID,
questionText,
result,
answer.query('.') AS answer
FROM
q CROSS APPLY
answers.nodes('answer') AS a(answer)
)
SELECT
qa.qID,
q.questionText,
q.result,
qa.answer.value('answer[1]', 'nvarchar(max)') AS answer,
qa.answer.value('answer[1]/@number', 'int') AS number,
qa.answer.value('answer[1]/@value', 'int') AS val,
qa.answer.value('answer[1]/@chosen', 'bit') AS chosen
FROM
qa INNER JOIN
q ON qa.qID = q.qID;
如何将此逻辑应用于每个表格行中的每个 XML 值?我需要显示
How can this logic be applied to every XML value, in every table row? I need to display
- 测验 HistoryId
- 该测验中的每个问题(为了清楚起见，带有可选 ID，尽管这是由 SQL 语句生成的，并且不存在于 XML 中)
- 每个问题的所有答案
我想要达到的最终结果会产生这样的结果:
The end result I am trying to achieve would produce something like this:
HistoryId qID questionText result answer number val chosen
--------- ---- --------------------------------------------------------------------------------------- ---------- ---------------------------------------------------------------------------------------- ------- ---- ------
100 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Powder 0 0 0
100 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Carbon Dioxide (CO2) 1 0 0
100 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Water (H2O) 2 1 1
100 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Foam 3 0 0
100 2 What should your immediate action be on hearing a fire alarm? Correct Find all of your colleagues before making a speedy exit together 0 0 0
100 2 What should your immediate action be on hearing a fire alarm? Correct Collect all your valuables before making a speedy exit 1 0 0
100 2 What should your immediate action be on hearing a fire alarm? Correct Check the weather to see if you need your coat before leaving 2 0 0
100 2 What should your immediate action be on hearing a fire alarm? Correct Leave the building by the nearest exit, closing doors behind you if the rooms are empty 3 1 1
101 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Powder 0 0 0
101 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Carbon Dioxide (CO2) 1 0 0
101 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Water (H2O) 2 1 1
101 1 Which fire extinguisher is most suitable for a waste paper basket fire? Correct Foam 3 0 0
101 2 Should you use lifts during a fire? Correct Yes 0 0 0
101 2 Should you use lifts during a fire? Correct No 1 1 1
101 3 Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating? Incorrect The body of the extinguisher 0 0 1
101 3 Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating? Incorrect The release trigger and the bottom of the extinguisher 1 0 0
101 3 Which part of a Carbon Dioxide (CO2) extinguisher should you not touch when operating? Incorrect The horn of the extinguisher 2 1 0
我很欣赏这会造成大量重复(因为每个答案都会重复问题)，但没关系.
I appreciate that this creates a large number of duplication (as the questions are repeated for each answer), but that's okay.
我有一个 SQL Fiddle，我一直在使用它，设置样本数据.
I have a SQL Fiddle which I've been working from, with sample data set up.
推荐答案
它可以通过一系列 3 CROSS APPLY 逐级缩短
It can be a bit shorter with a series of 3 CROSS APPLY, level by level
SELECT HistoryId,
t.qID,
t.questionText,
t.result,
a.aId,
a.answerNbr,
a.answerChosen,
a.answerTxt
FROM
tbl_QuizHistory
CROSS APPLY QuizData.nodes('quizresult') AS n(q)
CROSS APPLY (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID,
t.q.value('(./questionText)[1]', 'nvarchar(max)') AS questionText,
t.q.value('(./result)[1]', 'nvarchar(50)') AS result,
t.q.query('.') queryXml
FROM
n.q.nodes('./question') t(q)
) t
CROSS APPLY (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS aID,
q.a.value('(./@number)[1]', 'int') as answerNbr,
q.a.value('(./@chosen)[1]', 'bit') as answerChosen,
q.a.value('.','nvarchar(max)') as answerTxt
FROM
t.queryXml.nodes('question/answer') q(a)
) a;
如果不需要特定级别的计算(例如 row_number()):
If no level specific calculations (e.g. row_number()) are need:
SELECT HistoryId,
t.qID,
t.questionText,
t.result,
q.a.value('(./@number)[1]', 'int') as answerNbr,
q.a.value('(./@chosen)[1]', 'bit') as answerChosen,
q.a.value('.','nvarchar(max)') as answerTxt
FROM
tbl_QuizHistory
CROSS APPLY QuizData.nodes('quizresult') AS n(q)
CROSS APPLY (
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID,
t.q.value('(./questionText)[1]', 'nvarchar(max)') AS questionText,
t.q.value('(./result)[1]', 'nvarchar(50)') AS result,
t.q.query('.') queryXml
FROM n.q.nodes('./question') t(q)
) t
CROSS APPLY t.queryXml.nodes('question/answer') q(a)
这篇关于选择除结构化 XML 数据之外的行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
