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

Question

I've got a situation where I'm trying to get a list of unfilled fields from a temp table into a comma separated statement.

So given the example data (which will always be a single row, and probably in a temp table (as the actual data will come from a multitude of source tables)):

Field1     Field2   Field3   Field4
'aaa'      null      ''      null

And the mapping table of

FieldName    Question   Section
'Field1'     'Q1'       'Sec1'
'Field2'     'Q2'       'Sec1'
'Field3'     'Q3'       'Sec2'
'Field4'     'Q4'       'Sec2'

I would like the following result:

Section   UnansweredQs
'Sec1'    'Q2'
'Sec2'    'Q3, Q4'

I've got as far as the comma separated list of questions by 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 into #qlist values ('f1', 'q1', 'sec1'), ('f2', 'q2', 'sec1'), ('f3', 'q3', 'sec2'), ('f4', 'q4', 'sec2')

insert into #testData values ('asda', null, '', null)

Then

declare @usql nvarchar(max) = ''
declare @sql nvarchar(max)
declare @xml xml

--build a gargantuan set of union statements, comparing the column value to null/'' and putting q# if it is
set @usql = 
    (
    select 'select case when ' + c.name + ' is null or ' + c.Name + ' = '''' then ''' + q.question + ', '' else '''' end from #testData union '
    from tempdb..syscolumns c
    inner join #qlist q
        on c.name = q.fieldName
    where c.id = object_id('tempdb..#testData') 
    for xml path('')
    );
--remove the last 'union', append for xml path to pivot the rows into a single column of concatenated rows
set @usql = left(@usql, len(@usql) - 6) + ' for xml path('''')'

print @usql

--remove final comma
--get the position of the last comma in the select statment (ie after the final unanswered question)
declare @lastComma int = charindex(',', reverse(@usql))
--add the bit before the last comma, and the bit after the last comma but skip the actual comma :)
set @usql = left(@usql, len(@usql) - @lastComma) + right(@usql, @lastComma - 2)

exec (@usql)

With this I get

XML_F52E2B61-18A1-11d1-B105-00805F49916B
----------------------------------------
q2, q3, q4

But I can't get that result set into another table or variable (via insert into #tmpresult exec (@usql) approach).

Usually with the Msg 1086, Level 15, State 1, Line 1 The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it. error.

I've tried various things, wrapping, removing the unions, CTE's but can't get it to work.

Answer 1

I have a query for you:

with cte as (
    select
        N.Name
    from Table1
        cross apply (values
           ('Field1', Field1),
           ('Field2', Field2),
           ('Field3', Field3),
           ('Field4', Field4)
        ) as N(Name,Value)
    where N.Value is null or N.Value = ''
)
select distinct
    T2.Section,
    stuff(
        (
            select ', ' + TT.Question
            from Table2 as TT
                inner join cte as c on c.Name = TT.FieldName
            where TT.Section = T2.Section
            for xml path(''), type
        ).value('.', 'nvarchar(max)')
    , 1, 2, '') as UnansweredQs
from Table2 as T2

you can turn it into dynamic by yourself :)

sql fiddle demo