How do I Pivot on an XML column's attributes in T-SQL

sql, sql-server, t-sql, xml, xquery

Solution

Have a look at this dynamic pivot and more recently this one - you basically need to be able to `SELECT DISTINCT FieldName` to use this technique to build your query dynamically.

Here's the full answer for your particular problem (note that there is a column order weakness when generating the list from the distinct attributes in knowing what order the columns should appear):

DECLARE @template AS varchar(MAX)
SET @template = 'SELECT 
    FormEntryId
    ,{@col_list}
    ,DateCreated 
FROM FormEntry'

DECLARE @col_template AS varchar(MAX)
SET @col_template = 'FormXML.value(''/Root[1]/Form[1]/FormData[@FieldName="{FieldName}"][1]/@FieldValue'',''varchar(max)'') AS {FieldName}'

DECLARE @col_list AS varchar(MAX)

;WITH FieldNames AS (
    SELECT DISTINCT FieldName
    FROM FormEntry
    CROSS APPLY (
        SELECT X.FieldName.value('@FieldName', 'varchar(255)')
        FROM FormXML.nodes('/Root[1]/Form[1]/FormData') AS X(FieldName)
    ) AS Y (FieldName)
)
SELECT @col_list = COALESCE(@col_list + ',', '') + REPLACE(@col_template, '{FieldName}', FieldName)
FROM FieldNames

DECLARE @sql AS varchar(MAX)
SET @sql = REPLACE(@template, '{@col_list}', @col_list)

EXEC (@sql)

Problem

I need to perform a pivot on an XML column in a table, where the XML contains multiple elements with a number of attributes. The attributes in each element is always the same, however the number of elements will vary. Let me give an example... ``` FormEntryId | FormXML | DateCreated ==================================================================================== 1 |<Root> | 10/15/2009 | <Form> | | <FormData FieldName="Username" FieldValue="stevem" /> | | <FormData FieldName="FirstName" FieldValue="Steve" /> | | <FormData FieldName="LastName" FieldValue="Mesa" /> | | </Form> | |</Root> | | | ------------------------------------------------------------------------------------ 2 |<Root> | 10/16/2009 | <Form> | | <FormData FieldName="Username" FieldValue="bobs" /> | | <FormData FieldName="FirstName" FieldValue="Bob" /> | | <FormData FieldName="LastName" FieldValue="Suggs" /> | | <FormData FieldName="NewField" FieldValue="test" /> | | </Form> | |</Root> | ``` I need to wind up with a result set for each distinct FieldName attribute values (In this example, Username, FirstName, LastName, and NewField) with their corresponding FieldValue attributes as the value. The results for the example I gave above would look like: ``` FormEntryId | Username | FirstName | LastName | NewField | DateCreated ====================================================================== 1 | stevem | Steve | Mesa | NULL | 10/15/2009 ---------------------------------------------------------------------- 2 | bobs | Bob | Suggs | test | 10/16/2009 ``` I've figured out a way to accomplish this with static columns ``` SELECT FormEntryId, FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="Username"][1]/@FieldValue','varchar(max)') AS Username, FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="FirstName"][1]/@FieldValue','varchar(max)') AS FirstName, FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="LastName"][1]/@FieldValue','varchar(max)') AS LastName, FormXML.value('/Root[1]/Form[1]/FormData[@FieldName="NewField"][1]/@FieldValue','varchar(max)') AS NewField, DateCreated FROM FormEntry ``` However I would like to see if there's a method to have the columns be dynamic based on the distinct set of "FieldName" attribute values.

Original source

Related problems