SQL pivot dynamic columns out of an nvarchar column containing xml

Question

I have been given a table with the following columns and some example data:

ID    Title   FieldsXml [nvarchar(max)]
--    -----   -------------------------
1     A       <Fields><Field Name="X">x1</Field><Field Name="Y">y1</Field></Fields>
2     B       <Fields><Field Name="Y">y2</Field><Field Name="Z">z2</Field></Fields>
3     C       <Fields><Field Name="Z">z3</Field></Fields>

And I need to query it to get a result like this:

ID    Title   X   Y   Z
--    -----   --  --  --
1     A       x1  y1
2     B           y2  z2
3     C               z3

The xml field is supposedly guaranteed to be well formed and match the schema even though it is an nvarchar and not xml typed. However, the values of the Name attribute are not known ahead of time.

I am using SQL Server 2008. I can use a stored proc if necessary, but am looking for a solution that can avoid that and also avoid dynamic SQL. Is it impossible to write such a query?

If stored procs or dynamic sql are the only way, I am open to a solution using that.

Answer 1

-- Sample data
declare @T table
(
  ID int,
  Title nvarchar(10),
  FieldsXml nvarchar(max)
)
insert into @T values
(1,     'A',       '<Fields><Field Name="X">x1</Field><Field Name="Y">y1</Field></Fields>'),
(2,     'B',       '<Fields><Field Name="Y">y2</Field><Field Name="Z">z2</Field></Fields>'),
(3,     'C',       '<Fields><Field Name="Z">z3</Field></Fields>')

-- Create temp table 
select T.ID,
       T.Title,
       TN.X.value('@Name', 'nvarchar(128)') as FieldName,
       TN.X.value('.', 'nvarchar(max)') as FieldValue
into #tmp       
from @T as T     
  cross apply (select cast(FieldsXml as XML)) as TX(X)
  cross apply TX.X.nodes('/Fields/Field') as TN(X)  


declare @ColList nvarchar(max)
declare @Sql nvarchar(max)

-- Build column list
select @ColList = stuff((select '], ['+FieldName
                         from #tmp
                         group by FieldName
                         for xml path('')), 1, 2, '')+']'

-- Build query
set @Sql = 'select * 
            from (select ID,
                         Title, 
                         FieldName, 
                         FieldValue
                  from #tmp 
                 ) as T
            pivot (min(FieldValue) for FieldName in (' + @ColList + ')) as P'

exec (@Sql)

drop table #tmp