How Stuff Works Or Logic of Stuff & XML Path in SQL

Question

My Output is correct but I want to know how actually STUFF works.

I have the simple query which returns me total number of months between @startDate & @endDate.

I am storing that months into the the @cols by help of STUFF.

Query is like this :

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

1. "," in the query should print before the values but it print like below O/P.
2. If I remove XML Path from the stuff I am getting null value.
3. How actually STUFFworks with XML path

Here's My output :

 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

Solution

Stuff works on strings, the only thing its doing on your SQL is remove the initial comma from position 1. Without stuff it would look like ,a,b,c,d but when you stuff the position one with an empty value it transforms it to a,b,c,d

Your question is probably more about what FOR XML is doing. In this case, the FOR XML is being applied as a "trick" to concatenate all the rows from #tempDates in one long comma-separated string ,a,b,c,d and stuff is just removing that first comma.

For xml is creating a string x = ',a' + ',b' +',c' + ',d' so x ends up as ',a,b,c,d'

Stuff(x,1,1,'') is replacing only the comma in position 1 to 1 with '' therefore is now x='a,b,c,d'

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

Comments

[Hardik Parmar]

Thanx for edit @Gidil but I want help to understand stuff.

[Lasse V. Karlsen]

To be honest I think you should capitalize the word STUFF everywhere in your post, otherwise it just seems you want to know how stuff works, you know, stuff, things, thingamajigs.

[puzzlepiece87]

Possible duplicate of How Stuff and For Xml Path work in Sql Server

[Hardik Parmar]

Okay I got it but if first comma is only for replacement then hoe comma is coming after the value

[Hardik Parmar]

That's Great! Means first whole string will be form like as you have mentioned in the example then it XML will remove only first comma from it. I thought it delete comma from every row which stuff will get.

[Hardik Parmar]

Last question : XML path is only used for concatenating the string & why this '.' is used in my query.

[Jayvee]

'.' in xml path means 'current element' so basically you are taking the value of the current element (,a,b,c,d) as nvarchar and return it to the stuff function

[Hardik Parmar]

I appreciate your efforts. Thank you for your valuable time.