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.
-- 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