sql server - Inserting a dynamically built 'for xml' statement into a table or variable -

i've got situation i'm trying list of unfilled fields temp table comma separated statement.

so given example data (which single row, , in temp table (as actual data come multitude of source tables)):

field1     field2   field3   field4 'aaa'      null      ''      null 

and mapping table of

fieldname    question   section 'field1'     'q1'       'sec1' 'field2'     'q2'       'sec1' 'field3'     'q3'       'sec2' 'field4'     'q4'       'sec2' 

i following result:

section   unansweredqs 'sec1'    'q2' 'sec2'    'q3, q4' 

i've got far comma separated list of questions doing:

create table #testdata (f1 varchar(50), f2 int, f3 varchar(50), f4 varchar(50)) create table #qlist (fieldname varchar(5), question varchar(3), section varchar(5))  insert #qlist values ('f1', 'q1', 'sec1'), ('f2', 'q2', 'sec1'), ('f3', 'q3', 'sec2'), ('f4', 'q4', 'sec2')  insert #testdata values ('asda', null, '', null) 

then

declare @usql nvarchar(max) = '' declare @sql nvarchar(max) declare @xml xml  --build gargantuan set of union statements, comparing column value null/'' , putting q# if set @usql =      (     select 'select case when ' + c.name + ' null or ' + c.name + ' = '''' ''' + q.question + ', '' else '''' end #testdata union '     tempdb..syscolumns c     inner join #qlist q         on c.name = q.fieldname     c.id = object_id('tempdb..#testdata')      xml path('')     ); --remove last 'union', append xml path pivot rows single column of concatenated rows set @usql = left(@usql, len(@usql) - 6) + ' xml path('''')'  print @usql  --remove final comma --get position of last comma in select statment (ie after final unanswered question) declare @lastcomma int = charindex(',', reverse(@usql)) --add bit before last comma, , bit after last comma skip actual comma :) set @usql = left(@usql, len(@usql) - @lastcomma) + right(@usql, @lastcomma - 2)  exec (@usql) 

with get

xml_f52e2b61-18a1-11d1-b105-00805f49916b ---------------------------------------- q2, q3, q4 

but can't result set table or variable (via insert #tmpresult exec (@usql) approach).

usually msg 1086, level 15, state 1, line 1 xml clause invalid in views, inline functions, derived tables, , subqueries when contain set operator. work around, wrap select containing set operator using derived table syntax , apply xml on top of it. error.

i've tried various things, wrapping, removing unions, cte's can't work.

i have query you:

with cte (     select         n.name     table1         cross apply (values            ('field1', field1),            ('field2', field2),            ('field3', field3),            ('field4', field4)         ) n(name,value)     n.value null or n.value = '' ) select distinct     t2.section,     stuff(         (             select ', ' + tt.question             table2 tt                 inner join cte c on c.name = tt.fieldname             tt.section = t2.section             xml path(''), type         ).value('.', 'nvarchar(max)')     , 1, 2, '') unansweredqs table2 t2 

you can turn dynamic :)

sql fiddle demo