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

Question

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.

Answer 1

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)

Answer 2

Dynamic pivot isn't built into the language for good reason. It would be necessary to scan the entire table containing potential column names before the structure of the result were known. As a result, the table structure of the dynamic pivot statement would be unknown before run time. This creates many problems regarding parsing and interpretation of language.

If you decide to implement dynamic pivot on your own, watch out for SQL injection opportunities. Be sure to apply QUOTENAME or equivalent to the values you plan to use as column names in your result. Also consider what result you want if the number of distinct values in your source that will become column names exceeds the allowed number of columns of a result set.