How Stuff & XML Path Material or Logic Works in SQL

Question

My conclusion is correct, but I want to know how STUFF works.

I have a simple query that returns me the total number of months between @startDate and @endDate .

I save these months to @cols using STUFF .

The request is as follows:

 SELECT DISTINCT ',' + Quotename(CONVERT(CHAR(10), startdate, 120)) FROM #tempdates 

• The "," in the request must be printed before the values, but printed as shown below O / P.
• If I remove the XML path from the material, I get a null value.
• How STUFFworks really does with an XML outline

Here is my conclusion:

  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 < 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 

Answer 1

The material works on lines, the only thing it does on your SQL is to remove the starting comma from position 1. Without any thing, it will look like: a, b, c, d, but when you type in a position with an empty value, it converts it in a, b, c, d

Perhaps your question is more about what FOR XML does. In this case, FOR XML is used as a “trick” to combine all the lines from #tempDates into one long comma-separated line, a, b, c, d, and the material simply removes this first comma.

For xml, the line x = ', a' + ', b' + ', c' + ', d' is created, so x ends as ', a, b, c, d'

Stuff(x,1,1,'') replaces only the comma in position 1 with "c", so now x = 'a, b, c, d'

 [STUFF ( character_expression , start , length , replaceWith_expression )]