title: How Stuff Works Or Logic of Stuff & XML Path in SQL
body: <p>My Output is correct but I want to know how actually STUFF works. </p>
  
  <p>I have the simple query  which returns me total number of months between <code>@startDate</code> &amp; <code>@endDate</code>.</p>
  
  <p>I am storing that months into the the @cols by help of <strong>STUFF</strong>.</p>
  
  <p>Query is like this :</p>
  
  <pre><code>SELECT DISTINCT ',' 
                  + Quotename(CONVERT(CHAR(10), startdate, 120)) 
  FROM   #tempdates 
  </code></pre>
  
  <ol>
  <li>"," in the query should print before the values but it print like below O/P. </li>
  <li>If I remove XML Path from the stuff I am getting null value.</li>
  <li>How actually <strong>STUFFworks with XML path</strong></li>
  </ol>
  
  <p>Here's My output :</p>
  
  <p><img src="https://i.stack.imgur.com/hFzuH.png" alt="enter image description here"></p>
  
  <pre><code> DECLARE @cols AS NVARCHAR(MAX),   
              @query AS NVARCHAR(MAX),  
              @startdate datetime =  '1-Jan-2014',  
              @enddate datetime  =  '1-Jun-2014'
  
      ;with cte (StartDate, EndDate) as   
      (   
         select min(@startdate) StartDate, max(@enddate) EndDate   
         union all   
         select dateadd(mm, 1, StartDate), EndDate   
         from cte   
         where StartDate &lt; EndDate   
      )   
      select StartDate   
      into #tempDates   
      from cte   
  
      select @cols =  STUFF((SELECT distinct ',' + QUOTENAME(convert(CHAR(10), StartDate, 120))   
          from #tempDates   
          FOR XML PATH(''), TYPE   
          ).value('.', 'NVARCHAR(MAX)')   
          ,1,1,'') 
  
  
          select @cols
          drop table #tempDates
  </code></pre>