Please explain what does "for xml path(''), TYPE) .value('.', 'NVARCHAR(MAX)')" do in this code

A common technique for aggregate string concatenation before SQL Server 2017 is using XML data type methods along with STUFF to remove the extra delimiter. In SQL Server 2017 and later, STRING_AGG makes this task much cleaner and easier. The STRING_AGG equivalent:

select @colsUnpivot = STRING_AGG(quotename (C.name),',')
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

The components of the XML expression technique for aggregate string concatenation are as follows.

This subquery will generate a result set with one row per column in the source table with a comma preceding each column name:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')

Example result:

,[column1]
,[column2]
,[column3]

Adding the FOR XML PATH(''), TYPE converts these rows into a single strongly-typed XML text node with the concatenated rows, allowing XML methods like value to be used on the type:

select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
for xml path(''), TYPE

Result (XML type):

,[column1],[column2],[column3]

Invoking the method value('.', 'NVARCHAR(MAX)') on that XML node converts the XML node to an nvarchar(MAX) string. The value XML data type method takes an XQuery expression with '.' representing the current node (only node here) and the second argument being the desired SQL data type to be returned.

SELECT
         (select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)')

Result (nvarchar(MAX)):

,[column1],[column2],[column3]

Finally, the STUFF function removes the extraneous leading delimiter from the string. There is an error in your original example; the length should be 1 instead of 8 so that only the comma is removed:

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

Final result (nvarchar(MAX)):

[column1],[column2],[column3]

I sometimes see folks omit the , TYPE).value('.', 'NVARCHAR(MAX)') when using this technique. The problem with that is that some characters must be escaped with XML entity references (e.g; quotes) so the resultant string will not be as expected in that case.

EDIT:

Incorporating Mister Magoo's recommendation to use (./text())[1] instead of just . as the node spec, the query below will improve performance with large data sets.

SELECT
         STUFF((select ',' + quotename (C.name)
          from sys.columns c
          where c.object_id = OBJECT_ID('dbo.result2')
          for xml path(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'),1,1,'');

As Dan said, but also...

Get in the habit of using the text() node in xml values, it won't make much difference to a small result set, but I see no reason to ever NOT use it, and you will really see a benefit in larger data sets.

The red section is painful and can be avoided by the use of (./text())[1]