title: Concatenation of strings by for xml path
body: <p>
  Hi!
  Today I learned <code>for xml path</code> technique to concatenate strings in mssql. Since I've never worked with xml in mssql and google hasn't helped, I need to ask you.</p>
  
  <p>Let's imagine the default case. We need to concatenate some strings from a table:</p>
  
  <pre><code>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, '')
  </code></pre>
  
  <p>So I get smth like this:</p>
  
  <pre><code>[str1], [str2], [strn]
  </code></pre>
  
  <p>Ok. It works fine. But I have two very similar concatenate blocks. The difference is just in the way the result string looks like:</p>
  
  <ul>
  <li><code>[str1], [str2], [strn]</code>
  <br>and</li>
  <li><code>isnull([str1], 0) as [str1], isnull([str2], 0) as [str2], isnull([strn], 0) as [strn]</code></li>
  </ul>
  
  <p>So I can write 2 very similar code blocks (already done, btw) with different string constructors or to try extend previous code to has xml variable containing 2 kind of constructors and then concatenate by xml node type. Doing 2nd way I met some problems - I wrote this:</p>
  
  <pre><code>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, '')
  </code></pre>
  
  <p>but it raise error <code>"XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'"</code>. <br>
  To replace, e.g., <code>'/name'</code> to <code>'/name[1]'</code> or any other <code>'/name[x]'</code>, will return just x-th 'name' record but not all 'name' records concatenated. <br>
  <strong>[question]</strong>: is it possible to solve problem 2nd way like I want and if it's possible then how?<br>
  <strong>[disclaimer]</strong>: the problem isn't really serious for me now (1st way just a little bit uglier but also fine), but it seems very interesting how to come over :)
  Thanks! </p>