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

We can easily convert existing data into the XML format using this. We have the following modes available in the FOR XML clause. We can use the FOR XML clause to join or concatenate multiple columns into a single row output as well. Here is how SQL Server for XML Path works: Get XML element string with FOR XML. Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. ... Remove leading comma with STUFF. ... Join on id to get full list.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,'');