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

I need to do a pivot on the XML column in the table, where XML has many elements with many attributes in each element Properties are always the same, although the number of elements will vary. I give an example ...

  FormEntryId | Form xml Dated =============================================== == ======================================= Root & gt; | 10/15/2009 | & Lt; Form & gt; | | & Lt; Formdata field name = "user name" fieldval = "steam" /> | | & Lt; FormData field name = "firstname" field = value = "steve" /> | | & Lt; FormData field name = "lastname" field = value = "Mesa" /> | | & Lt; / Form & gt; | | & Lt; / Root & gt; | | | -------------------------------------------------- ---------------------------------- 2 | & Lt; Root & gt; | 10/16/2009 | & Lt; Form & gt; | | & Lt; FormData FieldName = "UserName" fieldWall = "Bobs" /> | | & Lt; FormData field name = "firstname" field = value = "bob" /> | | & Lt; FormData field name = "last name" field = value = "Suggs" /> | | & Lt; Formdata field name = "newfield" fieldval = "test" /> | | & Lt; / Form & gt; | | & Lt; / Root & gt; |  

Setting the result with their respective field value attributes as the result for each different filename attribute values ​​(in this example, the user name, first name, last name, and newfield) Will happen. Results shown for the above example I would like to see:

  FormEntryId | | Username First name Last name | Newfield | Dated =============================================== == ===================== 1 | Stevem | | Steve | Mesa | Faucet 10/15/2009 --------------------------------------------- ------------------------- 2 | Bobs | Bob | SGG | Test | 10/16/2009  

I have found a way to accomplish this with a static column

  SELECT FormEntryId, FormXML.value ('/ root [ 1] / form [1] / form data [@fielddenem = "user name"] [1] / field field ',' worm (max) ') username, formxml First name, form xml value ('/ root [1] / form [1] / form data [@fieldname ='] [firstname], / formdata [@fieldname = "value" Last name, 'varchar (max)') last name, formaxml.value ('/ root [1] / form [1] / form data [@finalname ='] "Newfield"] [1] / @FilitValue ',' Vertical (Max) '), AS Newfield, Date Entry form generated  

However, Want to see whether the column value is dynamic "Field" based on the set different specialties.

Take a quick look at - you should be able to basically make your query dynamically To use this technique, choose Inter FieldName .

Here the full answer is given for your particular problem (note that there is a column order weakness while creating a list of specific properties to know incoming orders in the column):

  DECLARE @template as varchar (MAX) SET @template = 'SELECT FormEntryId, {@ col_list}, the attested form entry' DECLARE @col_template AS varchar (MAX) SET @col_template = 'FormXML.value (' '/ root [ 1] / form [1] / form data [@filename = "{fieldname}"] [1] / @fieldwellell, '' varchar (max) '') {fieldname} 'DECLARE @col_list varchar (MAX) in the form of; Formed as Field Name (Select Field Entry Cross with Field Name (SELECT X.FieldName.value ('' FieldName '', 'varchar (255)') from FormXML.Node ('/ root [1] / form [1] / FormData) AS X (FieldName)) SELECT @col_list = COALESCE (@col_list + ',', '') + REPLACE (@col_template, '{FieldName}', FieldName) field name DECLARE @sql AS varchar (max) SET @sql = REPLACE (@ Template, '{@col_list}}', @col_list) EXEC (@sql)