Wählen Sie alle XML-Knoten aus XML-Feld in ein Tabellen-ähnliches Ergebnis in SQL server

Ich habe einen Tisch (MyTable) mit 3 Feldern darin.

ResponseID - Int ResponseType - Int Response - XML 

Jedes XML-Feld hat die gleiche XML-Struktur, wo der ResponseType gleich ist:

So könnte ich 10 datasätze mit XML ähnlich wie dies, weil die ResponseType ist "1":

  <details> <email>test@example.com</email> <name>Fred Smith</name> <dob>1950-01-01</dob> <address>22 some street</address> </details> 

Aber dann für ResponseType "2" könnte das XML aussehen:

  <details> <name>Fred Smith</name> <favouriteColor>Red</favouriteColor> </details> 

Was ich tun möchte, ist so etwas wie:

  SELECT Response.* FROM MyTable WHERE ResponseType = 2 

und gibt es zurück:

  name favouriteColor ---------------------- -------------- Fred Smith Red Betty Jones Pink 

Das Ziel ist es, alle data für einen bestimmten ResponseType auszumachen, um zu excel oder auf eine Webseite (asp.net c #)

Ich weiß, dass diese "Select" -statement nicht funktionieren wird, aber ich habe gesucht und jeder gibt Beispiele, die speziell alle Felder aus dem XML aussuchen, um auszuwählen. Ich möchte nicht wirklich dynamisches SQL in meinem SPROC build, um auszuführen und die data zu bekommen, aber wenn ich muss, werde ich. Ich habe eine andere Tabelle, die die data für jeden responseType beschreibt, aber ich mag wirklich keine dynamische SQL so.

Sofern du es nicht wünscht, andere Leute wie diese auszuprobieren, bitte ich nicht, dass du es tust. Einige hilfreiche Links oder Blog-Posts wäre perfekt. Oder ein "Du musst das mit Dynamic SQL machen" Antwort ist auch gut, wenn es so ist ..

Vielen Dank

Versuche eine dieser Lösungen:

 DECLARE @Response TABLE ( ResponseID INT, -- This column should have the smallest size: tinyint / smallint (don't waste the storage). Opinion: I really don't think that ResponseType column could have 1 million rows. ResponseType TINYINT, Response XML ); INSERT @Response VALUES (102, 1, N' <details> <email>test@example.com</email> <name>Fred Smith</name> <dob>1950-01-01</dob> <address>22 some street</address> </details>'); INSERT @Response VALUES (102, 2, N' <details> <name>Fred Smith</name> <favouriteColor>Red</favouriteColor> </details>'); -- Solution #1: Columns are static (for response type 2 this query query will show only two columns: name and favouriteColor). Every response type should have a separate query. SELECT name = tbl.Response.value('(details/name/text())[1]', 'NVARCHAR(100)'), favouriteColor = tbl.Response.value('(details/favouriteColor/text())[1]', 'NVARCHAR(100)') FROM @Response tbl WHERE tbl.ResponseType = 2; -- Note: replace the NVARCHAR(100) with the proper data type, max. length, precision and scale /* name favouriteColor ----------- -------------- Fred Smith Red */ -- Solution #2: there are only 3 columns: ResponseID, ElementName and ElementText. This query will show all child elements of <details> SELECT tbl.ResponseID, ElementName = x.XmlCol.value('local-name(.)', 'SYSNAME'), ElementText = x.XmlCol.value('(text())[1]', 'SYSNAME') FROM @Response tbl CROSS APPLY tbl.Response.nodes('(details/*)') x(XmlCol) WHERE tbl.ResponseType = 2; /* ResponseID ElementName ElementText ---------- -------------- ----------- 102 name Fred Smith 102 favouriteColor Red */