How can i get XML using a Dynamic Query ?

i have a function like this, but i alwasys says wrong:

alter function GROUP_CONCAT(@.tableName varchar(100),@.groupByColumn varchar(100),@.targetColumn varchar(100),@.targetValue varchar(100))

returns varchar(1000)

as

begin

declare @.back varchar(1000)

declare @.sql varchar(1000)

set @.sql = 'select '+@.targetColumn+' from ' + @.tableName + ' where '+@.groupByColumn + ' = '''+@.targetValue + ''' for xml auto, root(''root'')'

declare @.x xml

set @.x = exec(@.sql)

return @.sql

end

I want to generate XML using a exec(...) , that is a Dynamic Query, but SQL Server 2005 always say something wrong with exec(....)

How can i fix it? Thanks

The reason is that you cannot use exec() as an expression. It is a statement that cannot be assigned.

If you just want to return it over TDS, do not assign it to a variable, otherwise assign it to a temp table inside the constructed node and then retrieve it from there.

Also, if you assign the FOR XML to a column or variable of type XML, it is better to add the type directive to the FOR XML as in FOR XML PATH, TYPE, ROOT('root'). That avoids unnecessary parsing.

Best regards

Michael