T-SQL Transform/Pivot XML Data to usable Data Rows using XQuery language elements

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.

Leave a Reply

Your email address will not be published. Required fields are marked *