Select row data in addition to structured XML data

Question

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).

The table structure is this:

CREATE TABLE tbl_QuizHistory (
  HistoryId int PRIMARY KEY,
  QuizData xml NOT NULL
);

Each QuizData row value is similar to this:

<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>

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;

How can this logic be applied to every XML value, in every table row? I need to display

The quiz HistoryId
Each question from that quiz (with optional ID for clarity, although this was generated by the SQL statement, and doesn't exist in the XML)
All the answers for each question

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.

I have a SQL Fiddle which I've been working from, with sample data set up.

Lukasz Szozda · Answer 1 · 2017-08-29T14:36:01.377

If I understand you correctly you want:

;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 tbl_QuizHistory t
    CROSS APPLY t.QuizData.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 
JOIN q ON qa.qID = q.qID;

Rextester Demo

Or even shorter:

;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,
        answer.query('.') AS answer
    FROM tbl_QuizHistory t
    CROSS APPLY t.QuizData.nodes('/quizresult/question') AS n (q)
    CROSS APPLY n.q.nodes('answer') AS a(answer)
)
SELECT 
    q.qID,
    q.questionText,
    q.result,
    answer.value('answer[1]', 'nvarchar(max)') AS answer,
    answer.value('answer[1]/@number', 'int') AS number,
    answer.value('answer[1]/@value', 'int') AS val,
    answer.value('answer[1]/@chosen', 'bit') AS chosen
FROM q;

Rextester Demo 2

EDIT:

;WITH q AS (
    SELECT
        t.HistoryId,
        ROW_NUMBER() OVER(PARTITION BY t.HistoryId 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,
        answer.query('.') AS answer
    FROM tbl_QuizHistory t
    CROSS APPLY t.QuizData.nodes('/quizresult/question') AS n (q)
    CROSS APPLY n.q.nodes('answer') AS a(answer)
)
SELECT 
    q.HistoryId,
    q.qID,
    q.questionText,
    q.result,
    answer.value('answer[1]', 'nvarchar(max)') AS answer,
    answer.value('answer[1]/@number', 'int') AS number,
    answer.value('answer[1]/@value', 'int') AS val,
    answer.value('answer[1]/@chosen', 'bit') AS chosen
FROM q;

Almost... The qID is being generated incorrectly in both cases. This should reset back to 1 for the next HistoryId. I also need the HistoryId within the results please. — EvilDr, Aug 29 '17 at 14:03
@EvilDr `ROW_NUMBER() OVER ( PARTITION BY t.History_ID ORDER BY (SELECT NULL)) AS qID,` Please check updated answer — Lukasz Szozda, Aug 29 '17 at 14:34
Hi. I pasted the revision into Rextester, but the problem remains. In result row 1-4, that is the same question, but with four answers, so the qID should be the same (`1`) (see my example in the OP). In result row 5, the question text changes, so that is a new question, qId should be `2` for the next three rows. In row 9, that is a different quiz, so the qID should reset to 1. Does that make sense? — EvilDr, Aug 31 '17 at 11:37

Vladimir Baranov · Answer 2 · 2017-09-04T09:56:56.990

I think that the most straight-forward method to achieve it is to wrap your code that works for a given variable into a table-valued function. You can achieve the same result by in-lining your query, but with a complex code like yours it is much more readable if you use a function. Performance would stay the same, because it is an "inline" table-valued function, not a multi-statement function.

See for example, When would you use a table-valued function?

Function

CREATE FUNCTION [dbo].[GetQuizData]
(
    @ParamQuizData xml
)
RETURNS TABLE
AS
RETURN
(
    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
        @ParamQuizData.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
)

Main query

SELECT
    tbl_QuizHistory.HistoryID
    ,Q.*
FROM
    tbl_QuizHistory
    CROSS APPLY [dbo].[GetQuizData](tbl_QuizHistory.QuizData) AS Q
;

See SQL Fiddle

Disclaimer: I didn't analyse your code from the question for correctness. I simply wrapped it into the function, assuming that it works as you need it to work.

You can inline the long query from the TVF into the CROSS APPLY manually. You'll have to inline CTE as well and it will look ugly. You can compare execution plans of this variant and variant with TVF. They should be the same.

Here is SQL Fiddle.

Inlined query

SELECT
    tbl_QuizHistory.HistoryID
    ,CA.*
FROM
    tbl_QuizHistory
    CROSS APPLY 
    (
        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 
            (
                SELECT 
                    qID, 
                    questionText, 
                    result, 
                    answer.query('.') AS answer
                FROM 
                    (
                        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
                            tbl_QuizHistory.QuizData.nodes('/quizresult/question') AS n (q)
                    ) AS q0
                    CROSS APPLY 
                    answers.nodes('answer') AS a(answer)
            ) AS qa
            INNER JOIN 
            (
                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
                    tbl_QuizHistory.QuizData.nodes('/quizresult/question') AS n (q)
            ) AS q 
            ON qa.qID = q.qID
    ) AS CA
;

This long query may be simplified, but I didn't analyse what it does and how it does it. I simply inlined the given working query.

Thank you for this. While this works well, could you also please show an example *without* the function (e.g. a much longer SQL statement than you would like to see)? I'm trying to understand how the parts of the statement are fixed together around `APPLY`, and cannot figure it out. Thank you. — EvilDr, Sep 04 '17 at 09:18
@EvilDr, there is nothing special when you inline the function, it needs only some attention to details. At first I inlined CTE in the function. Just literally put the `SELECT` sub-queries instead of their references. Then put the query from the function inside the `CROSS APPLY` brackets. I updated the answer. — Vladimir Baranov, Sep 04 '17 at 09:55
Absolutely fantastic. So much easier to learn when I can see how the pieces fit together. I'll award the bounty when the time limit expires. Thank you. — EvilDr, Sep 04 '17 at 10:01

Serg · Accepted Answer · 2017-09-04T11:20:39.303

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;

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)

Demo

That's interesting. Could you please expand to show the answer level of detail as per the other answers, so I can see how interrogration is done at that level? — EvilDr, Sep 04 '17 at 10:44

Select row data in addition to structured XML data

3 Answers3

Linked