convert xml string in a sql table to dynamic columns

[fadimanar]

I have two tables (using table variables for illustration. You can run these directly in management studio) that are related by the Id column. Items in the first table has some standard set of columns and the second table has some extended parameter data for the same record. I'm storing the extended set as xml as it is dynamic in all aspects (different per product or new values being added etc).I'm able to join these two tables and flatten out the column list as you can see in the example below. But my query requires to have the dynamic columns be defined beforehand. I would like to have this truly dynamic in the sense that if I were to add a new column in the @extended table, it should automatically come out as a new column in the output column list. Basically the list of additional columns should be determined by the xml for that record. column name should be the xml tag and value should be value for the xml tag for each id.Any pointers? (and can it be fast too with around 100k records or more in each table)\[code\]declare @standard table( Id INT, Column1 varchar(10), Column2 varchar(10), Column3 varchar(10))declare @extended table( Id INT, column1 xml)insert into @standard values (1,'11', '12', '13')insert into @standard values (2,'21', '22', '23')insert into @extended values (1,'<FieldSet><Field><id>1</id><column4>1x</column4><column5>4x</column5></Field></FieldSet>')insert into @extended values (2,'<FieldSet><Field><id>2</id><column4>2x</column4><column5>5x</column5></Field></FieldSet>')select s.column1, s.column2,( SELECT Item2.value('(column4)[1]', 'varchar(50)') FROM e.column1.nodes('/FieldSet') AS T(Item) CROSS APPLY e.column1.nodes('/FieldSet/Field') AS T2(Item2) ) column4, ( SELECT Item2.value('(column5)[1]', 'varchar(50)') FROM e.column1.nodes('/FieldSet') AS T(Item) CROSS APPLY e.column1.nodes('/FieldSet/Field') AS T2(Item2) ) column5 from @extended ejoin @standard s on s.Id = e.Id\[/code\]