Concatenating strings in xml path

Question

Hi! Today I learned a for xml path method for xml path concatenating strings in mssql. Since I never worked with xml in mssql, and google did not help, I need to ask you.

Imagine a default case. We need to concatenate some rows from the table:

 declare @xmlRepNames xml = ( select ', [' + report_name + ']' from ( select distinct report_order, report_name from #report ) x order by report_order for xml path(''), type) select stuff((select @xmlRepNames.value('.', 'nvarchar(max)')), 1, 1, '') 

So, I get something like this:

 [str1], [str2], [strn] 

Ok It is working fine. But I have two very similar concatenate blocks. The only difference is what the result string looks like:

• [str1], [str2], [strn]
  and
• isnull([str1], 0) as [str1], isnull([str2], 0) as [str2], isnull([strn], 0) as [strn]

Therefore, I can write 2 very similar blocks of code (already done, btw) with different line constructors or try to extend the previous code to have an xml variable containing 2 types of constructors, and then combine them as xml node. Performing the second method, I met some problems - I wrote this:

 declare @xmlRepNames xml = ( select ', [' + report_name + ']' as name, ', isnull([' + report_name + '], 0) as [' + report_name + ']' as res from ( select distinct report_order, report_name from #report ) x order by report_order for xml path(''), type) select stuff((select @xmlRepNames.value('/name', 'nvarchar(max)')), 1, 1, ''), stuff((select @xmlRepNames.value('/res', 'nvarchar(max)')), 1, 1, '') 

but it causes the error "XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'" .
To replace, for example, from '/name' to '/name[1]' or any other '/name[x]' , only the x-th 'name' record will be returned, but not all 'name' records combined.
[question] : is it possible to solve the problem of the 2nd method, as I want, and if it is possible, then how?
[disclaimer] : the problem for me is not very serious right now (the 1st path is a bit ugly, but also beautiful), but it seems very interesting how to come :) Thank you!

Answer 1

Subquery cannot return two values. If you just want to concatenate strings, you don't need the xml data type at all. You can do stuff() and the subquery in one statement:

 declare @Rep1Names nvarchar(max) = ( stuff((select ', [' + report_name + ']' as name from (select distinct report_order, report_name from #report ) x order by report_order for xml path('') ) ), 1, 1, ''); declare @Rep2Names nvarchar(max) = ( stuff(select ', isnull([' + report_name + '], 0) as [' + report_name + ']' as res from (select distinct report_order, report_name from #report ) x order by report_order for xml path('') ) ), 1, 1, ''); 

Answer 2

Ok, so I was not completely satisfied with what Gordon Linoff suggested, and since I found this problem for me, I will add another solution without using for xml path :

 declare @pivot_sequence nvarchar(max), @columns_sequence nvarchar(max) select @pivot_sequence = coalesce(@pivot_sequence + ', [', '[') + col_name + ']', @columns_sequence = coalesce(@columns_sequence + ', ', '') + 'isnull([' + col_name + '], 0) as [' + col_name + ']' from some_table order by /* some_columns if needed to order concatenation */ 

Obviously, it will work much slower, but in cases where you do not have many lines, it will not greatly affect performance. In my case, I have a dynamic summary query and these rows are built for it - I will not have many columns in the summary, so this is good for me.