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.

