none
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

     

    Thursday, August 18, 2011 1:16 PM

Answers

  • 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)
    

    Thursday, August 18, 2011 10:44 PM
    Answerer