Dynamic query from XML field in SQL Server

Question

Hi,

We collect data from suppliers that has core fields that are held as standard relational data but variable supplier specific fields that are held in an xml SupplierColumns field. What I would like to have is a procedure that we can run that, based on the supplier in question, pulls back all of their fields in a table format i.e. all the standard relational fields then the xml fields flattened out. There are a variety of field headings and numbers of fields dependant upon the supplier.

 

Has anybody got any ideas? I inherited the database and am not familiar with xml fields...

Cheers

David

 

Answer 1

This is possible using dynamic SQL (Option 2 below aka dynamic pivot) but I wonder what kind of application can process a resultset of any shape.  You might be better off returning the results as rows instead of columns (Option 1) below.  Reporting Services for example could flatten this out using groupings or a matrix control.  Anyway try this demo which shows both techniques and let us know how you get on:

USE tempdb
GO

IF OBJECT_ID('tempdb..#suppliers') IS NOT NULL
DROP TABLE #suppliers
GO

CREATE TABLE #suppliers ( supplierId INT IDENTITY PRIMARY KEY, supplierName VARCHAR(50) UNIQUE NOT NULL, dateAdded DATETIME DEFAULT GETDATE(), SupplierColumns XML )
GO

INSERT INTO #suppliers ( supplierName, SupplierColumns )
VALUES 
	( 'David', '<SupplierData><field1>val1</field1><field2>val2</field2></SupplierData>' ),
	( 'wBob', '<SupplierData><field3>val3</field3><field4>val4</field4></SupplierData>' )


-- Option 1: Return core fields and XML fields as rows not columns
SELECT supplierId, supplierName, 
	sd.c.value('local-name(.)', 'VARCHAR(MAX)') AS elementName,
	sd.c.value('.', 'VARCHAR(MAX)') AS elementValue
FROM #suppliers s
	CROSS APPLY SupplierColumns.nodes('/SupplierData/*') sd(c)


-- Option 2: Dynamic Pivot
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO

SELECT supplierId, supplierName, 
	sd.c.value('local-name(.)', 'VARCHAR(MAX)') AS elementName,
	sd.c.value('.', 'VARCHAR(MAX)') AS elementValue
INTO #tmp
FROM #suppliers s
	CROSS APPLY SupplierColumns.nodes('/SupplierData/*') sd(c)
GO

DECLARE @sql			NVARCHAR(MAX)
DECLARE @dynamic_pivot	NVARCHAR(MAX)

SET @dynamic_pivot = STUFF(( SELECT ',' + elementName FROM #tmp FOR XML RAW, ELEMENTS, TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '' )

SET @sql = 'SELECT *
FROM #tmp us
	PIVOT ( MAX( elementValue ) FOR elementName In ( ' + @dynamic_pivot + ' ) ) AS pvt'

EXEC(@sql)