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.

score 1 · Accepted Answer · answered Aug 16 '13 at 12:50

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

actually you can do this with subquery, but for me cte is much cleaner — Roman Pekar, Aug 16 '13 at 13:47

score 1 · Answer 2 · answered Aug 18 '13 at 19:00

There is no need to use dynamic SQL to do this.

declare @X xml

set @X = (
         select *
         from #testData
         for xml path('root'), elements xsinil, type 
         )

select section,
       (
       select ', '+Q2.question
       from #qlist as Q2
       where Q1.section = Q2.section and
             @X.exist('/root/*[local-name() = sql:column("Q2.fieldName")][. = ""]') = 1
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as UnansweredQs
from #qlist as Q1
group by Q1.section

SQL Fiddle

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

2 Answers2