The Key to get this XML-2-Rows solution to work is to use an embedded .query(‘./Wert’) as Wert to enumerate each attribute’s value beneath a given element type dsData(b) and bsDatas(b).
Another important point is to add a ROW_NUMBER() to each value’s line to allow allocation of values between different element types.
The last WHERE statement demonstrates how values of a single row (7) or a specific element 50017 are selected.
CREATE TABLE unittest.XmlAuswertung (ID int IDENTITY(1,1) NOT NULL, XmlResult xml NOT NULL) INSERT INTO unittest.XmlAuswertung (XmlResult) VALUES (N'YOURXMLWITHOUT<?xml...?>HEADER'); DECLARE @xml XML; SELECT TOP 1 @xml = XmlResult FROM unittest.XmlAuswertung; SELECT def.value('@FeldId', 'int') as DefFeldID , def.value('Name[1]', 'varchar(255)') as DefName --, def.value('.', 'varchar(100)') as DefTitle FROM @xml.nodes('/Auswertungsreport/Listen/Liste/Listfelder/Definition') as definitionen(def) ; SELECT c.value('CountRecords[1]', 'int') as CountOfRecords FROM @xml.nodes('/Auswertungsreport/Listen/Liste/DatenSpalten') as CountOfRecords(c) ; --SELECT b.value('@FeldId', 'int') as BDFeldID -- , b.query('./Wert') Werte --FROM @xml.nodes('/Auswertungsreport/Listen/Liste/DatenSpalten/BaseData') as baseDatas(b) --SELECT -- bWerte.BDFeldID -- , ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rowId -- , w.value('.', 'nvarchar(4000)') as Wert --FROM --( -- SELECT b.value('@FeldId', 'int') as BDFeldID -- , b.query('./Wert') Werte -- FROM @xml.nodes('/Auswertungsreport/Listen/Liste/DatenSpalten/BaseData') as baseDatas(b) --) bWerte --CROSS APPLY bWerte.Werte.nodes('./Wert') as Werte(w) ;WITH felder AS ( SELECT def.value('@FeldId', 'int') as DefFeldID , def.value('Name[1]', 'varchar(255)') as DefName , def.value('FListNameShort[1]', 'varchar(50)') as DefAbk --, def.value('.', 'varchar(100)') as DefTitle FROM @xml.nodes('/Auswertungsreport/Listen/Liste/Listfelder/Definition') as definitionen(def) ) , werte AS ( SELECT bWerte.FeldID , ROW_NUMBER() OVER ( PARTITION BY FeldID ORDER BY ( SELECT 1 ) ) rowId , w.value('.', 'nvarchar(4000)') as Wert FROM ( SELECT b.value('@FeldId', 'int') as FeldID , b.query('./Wert') Werte FROM @xml.nodes('/Auswertungsreport/Listen/Liste/DatenSpalten/BaseData') as baseDatas(b) ) bWerte OUTER APPLY bWerte.Werte.nodes('./Wert') as Werte(w) UNION ALL SELECT bWerte.FeldID , ROW_NUMBER() OVER ( PARTITION BY FeldID ORDER BY ( SELECT 1 ) ) rowId , w.value('.', 'nvarchar(4000)') as Wert FROM ( SELECT b.value('@FeldId', 'int') as FeldID , b.query('./Wert') Werte FROM @xml.nodes('/Auswertungsreport/Listen/Liste/DatenSpalten/DatenSpalte') as dsData(b) ) bWerte OUTER APPLY bWerte.Werte.nodes('./Wert') as Werte(w) ) SELECT f.*, w.rowId, w.Wert FROM felder f INNER JOIN werte w ON w.FeldID = f.DefFeldID WHERE w.rowId = 7 OR w.FeldID = 50017
If you select and executing all SQL text starting at DECLARE @xml XML;
The following three result sets would be returned to client.
(1) the Field Definitions Ids and Names (DefFeldID, DefName)
(2) a selected single value CountOfRecords
(3) the big result joining the Field definition and the corresponding values of <WERT>Data</Wert> in XML data.
