sql server - How to select output of XML Path query in SQL? -

doc table contains lot of columns (even not used ones):

doc_ddfid doc_rentdate doc_returndate etc. --------- ------------ -------------- ---- 1         2012-07-28   2012-07-28

but want query used ones within doc's table.

docdefinitionfields list columsn in use document:

select dfl_colname  docdefinitionfields  dfl_ddfid = 1

docdefinitionfields:

dfl_colname ----------- doc_rentdate doc_returndate ...........

so want select columns (listed second query) doc table.

example (if 2 columns added document definition form want select them):

doc:

doc_rentdate doc_returndate ------------ -------------- 2012-07-28   2012-07-28

tried subquerying select concatenation of fields using xml path:

select     (select         dfl_colname + ', '         docdefinitionfields         xml path('')     )  doc

it's not simple tho. suggest?

what need here dynamic sql, this:

declare @sql varchar(max)  set @sql = 'select ' + stuff((select ', ' + dfl_colname docdefinitionfields xml path('') ),1,1,'') + ' doc'  exec (@sql)

also, in order eliminate additional comma(,) @ end of columns have added stuff function along xml path.

Kiastu