将 XML 数据读入分层表 [英] Read XML data into hierarchical tables

问题描述

我有用于存储测验结果的 XML 数据.我需要将它转换成两个表，一个包含问题，另一个包含答案，但至关重要的是，它们之间有关系.

I have XML data that is used to store quiz results. I need to convert this into two tables, one containing the questions, and the other containing the answers, but crucially, have a relation between them.

目前这个关系只存在于 XML 结构中(没有 ID 值等).

Currently this relation only exists in the XML structure (there are no ID values, etc.).

经过一天的研究和测试不同的方法后，我已经提取了两个部分，但无法弄清楚如何创建层次结构:

After a day of research and testing out different approaches, I've got as far as extracting the two parts, but cannot figure out how to create the hierarchy:

declare @xml xml = N'<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>What should your immediate action be on hearing a fire alarm?</questionText>
    <answer number="0" value="0" chosen="0" imageURL="">Find all of your colleagues before making a speedy exit together</answer>
    <answer number="1" value="0" chosen="0" imageURL="">Collect all your valuables before making a speedy exit</answer>
    <answer number="2" value="0" chosen="0" imageURL="">Check the weather to see if you need your coat before leaving</answer>
    <answer number="3" value="1" chosen="1" imageURL="">Leave the building by the nearest exit, closing doors behind you if the rooms are empty</answer>
    <result>Correct</result>
  </question>
  <question>
    <questionText>Which is the most suitable extinguisher for a Computer which is on fire?</questionText>
    <answer number="0" value="0" chosen="1" imageURL="">Water (H2O)</answer>
    <answer number="1" value="0" chosen="0" imageURL="">Powder</answer>
    <answer number="2" value="1" chosen="0" imageURL="">Carbon Dioxide (CO2)</answer>
    <result>Incorrect</result>
  </question>
</quizresult>';

-- Get questions only

DECLARE @questions TABLE (questionText nvarchar(max), result nvarchar(50));
INSERT INTO @questions (questionText, result)
SELECT
    n.q.value('(./questionText)[1]', 'nvarchar(max)') AS questionText,
    n.q.value('(./result)[1]', 'nvarchar(50)') AS result
FROM
    @xml.nodes('/quizresult/question') AS n (q);

-- Get answers only

DECLARE @answers TABLE (answer nvarchar(max), number int, val int, chosen bit);
INSERT INTO @answers (answer, number, val, chosen)
SELECT
    n.q.value('.[1]', 'nvarchar(max)') AS answer,
    n.q.value('@number', 'int') AS number,
    n.q.value('@value', 'int') AS val,
    n.q.value('@chosen', 'bit') AS chosen
FROM
    @xml.nodes('/quizresult/question/answer') AS n (q);

如果可以创建 ID/GUID(或其他东西)来创建尊重 XML 文件的父/子层次结构，任何人都可以启发我吗?我应该补充一点，实际上这是一个 XML 列，数据将被整体转换.在弄清楚基本方法之前，我只是在使用变量.

Can anyone please enlighten me if it is possible to create IDs/GUID's (or something) to create the parent/child hierarchy that respects the XML file? I should add, in actual fact this is an XML column and the data will be transformed en-masse. I'm just using a variable until I figure out the basic approach.

推荐答案

我们可以(ab)使用 ROW_NUMBER() 在 XQuery 之外生成 ID.序言:

We can (ab)use ROW_NUMBER() to generate the IDs outside XQuery. Preamble:

WITH questions AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS ID,
        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)
), questions_and_answers AS (
    SELECT ID, questionText, result, answer.query('.') AS answer
    FROM questions
    CROSS APPLY answers.nodes('answer') AS a(answer)
)

现在用

SELECT ID, questionText, result 
FROM questions

和答案

SELECT ID AS questionID,
    q.answer.value('answer[1]', 'nvarchar(max)') AS answer,
    q.answer.value('answer[1]/@number', 'int') AS number,
    q.answer.value('answer[1]/@value', 'int') AS val,
    q.answer.value('answer[1]/@chosen', 'bit') AS chosen
FROM questions_and_answers AS q

这篇关于将 XML 数据读入分层表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！