Wählen Sie neben strukturierten XML-data Zeilendaten aus

Ich weiß, wie man eine XML-Variable zu anderen Tabellen join kann, aber in diesem Fall versuche ich, jede Zeile aus einer Tabelle und die Struktur des XML aus jeder jeweiligen Tabellenzeile neben dieser Zeile auszuwählen. Ich kann keine Beispiele online finden, um mit diesem zu helfen, da die meisten Beispiele mit einem einzigen XML-Wert umgehen (entschuldigt, wenn es gibt, konnte ich sie nicht unter den unzähligen anderen XML-Beispielen finden).

Die Tabellenstruktur ist:

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

Jeder QuizData ähnelt diesem:

 <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 einer früheren Frage wurde gezeigt, wie die XML-data hierarchisch dargestellt werden können ( @xml ==> questions ==> answer(s) ), aber nur für einen einzigen XML-Wert, den ich angepasst habe, um die Frage- / Antworthierarchie in eine zu migrieren Tabelle:

 -- Works for a single XML value/variable... ;WITH q AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID, nqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, nqvalue('(./result)[1]', 'nvarchar(50)') AS result, nqquery('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; 

Wie kann diese Logik auf jeden XML-Wert angewendet werden, in jeder Tabellenzeile? Ich muss anzeigen

  1. Das Quiz HistoryId
  2. Jede Frage aus diesem Quiz (mit optionaler ID für Klarheit, obwohl dies von der SQL-statement generiert wurde und nicht im XML existiert)
  3. Alle Antworten für jede Frage

Das Endergebnis, das ich zu erreichen versuche, würde so etwas produzieren:

 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 

Ich schätze, dass dies eine große Anzahl von Duplikationen schafft (wie die Fragen für jede Antwort wiederholt werden), aber das ist okay.

Ich habe eine SQL-Geige, von der ich gearbeitet habe, mit Beispieldaten eingerichtet.

Es kann ein bisschen kürzer sein mit einer Reihe von 3 CROSS APPLY, Ebene nach 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, tqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, tqvalue('(./result)[1]', 'nvarchar(50)') AS result, tqquery('.') queryXml FROM nqnodes('./question') t(q) ) t CROSS APPLY ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS aID, qavalue('(./@number)[1]', 'int') as answerNbr, qavalue('(./@chosen)[1]', 'bit') as answerChosen, qavalue('.','nvarchar(max)') as answerTxt FROM t.queryXml.nodes('question/answer') q(a) ) a; 

Wenn keine row_number() Berechnungen (zB row_number() ) erforderlich sind:

  SELECT HistoryId, t.qID, t.questionText, t.result, qavalue('(./@number)[1]', 'int') as answerNbr, qavalue('(./@chosen)[1]', 'bit') as answerChosen, qavalue('.','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, tqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, tqvalue('(./result)[1]', 'nvarchar(50)') AS result, tqquery('.') queryXml FROM nqnodes('./question') t(q) ) t CROSS APPLY t.queryXml.nodes('question/answer') q(a) 

Demo

Wenn ich dich richtig verstehe, willst du:

 ;WITH q AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID, nqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, nqvalue('(./result)[1]', 'nvarchar(50)') AS result, nqquery('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; 

Reester-Demo


Oder noch kürzer:

 ;WITH q AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID, nqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, nqvalue('(./result)[1]', 'nvarchar(50)') AS result, nqquery('answer') AS answers, answer.query('.') AS answer FROM tbl_QuizHistory t CROSS APPLY t.QuizData.nodes('/quizresult/question') AS n (q) CROSS APPLY nqnodes('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; 

Reester-Demo 2

BEARBEITEN:

 ;WITH q AS ( SELECT t.HistoryId, ROW_NUMBER() OVER(PARTITION BY t.HistoryId ORDER BY(SELECT NULL)) AS qID, nqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, nqvalue('(./result)[1]', 'nvarchar(50)') AS result, nqquery('answer') AS answers, answer.query('.') AS answer FROM tbl_QuizHistory t CROSS APPLY t.QuizData.nodes('/quizresult/question') AS n (q) CROSS APPLY nqnodes('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; 

Ich denke, dass die einfachste Methode, um es zu erreichen ist, um Ihren Code, der für eine gegebene Variable in eine Tabelle-geschätzte function zu wickeln. Sie können das gleiche Ergebnis erzielen, indem Sie Ihre Abfrage insert, aber mit einem komplexen Code wie Sie ist es viel besser lesbar, wenn Sie eine function verwenden. performance würde gleich bleiben, denn es ist eine "Inline" Tabellenwertfunktion, nicht eine Multi-statementsfunktion.

Siehe z. B. Wann würdest du eine Tabellenwertfunktion verwenden?

function

 CREATE FUNCTION [dbo].[GetQuizData] ( @ParamQuizData xml ) RETURNS TABLE AS RETURN ( WITH q AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS qID, nqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, nqvalue('(./result)[1]', 'nvarchar(50)') AS result, nqquery('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 ) 

Hauptabfrage

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

Siehe SQL Fiddle


Haftungsausschluss: Ich habe Ihren Code nicht aus der Frage nach Richtigkeit analysiert. Ich habe es einfach in die function eingehüllt, vorausgesetzt, dass es funktioniert, wie Sie es brauchen, um zu arbeiten.


Sie können die lange Abfrage aus dem TVF in die CROSS APPLY manuell CROSS APPLY . Du musst auch CTE inline setzen und es wird hässlich aussehen. Sie können Ausführungspläne dieser Variante und Variante mit TVF vergleichen. Sie sollten gleich sein.

Hier ist SQL Fiddle .

Inlinierte Abfrage

 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, nqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, nqvalue('(./result)[1]', 'nvarchar(50)') AS result, nqquery('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, nqvalue('(./questionText)[1]', 'nvarchar(max)') AS questionText, nqvalue('(./result)[1]', 'nvarchar(50)') AS result, nqquery('answer') AS answers FROM tbl_QuizHistory.QuizData.nodes('/quizresult/question') AS n (q) ) AS q ON qa.qID = q.qID ) AS CA ; 

Diese lange Abfrage kann vereinfacht werden, aber ich habe nicht analysiert, was es tut und wie es das macht. Ich habe die gegebene Arbeitsabfrage einfach inlineiert.