What is exactly XML Path?

Question

Can someone please explain with more detail what is actually SELECT (STUFF SELECT...))FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)')

From what I know: XmlPath('')to concatenate column data into single row. Stuff is used to remove the first ‘,’ after string concatenation.

So what about TYPE).value('.','NVARCHAR(max)') use for ?

Answer 1

So what about TYPE).value('.','NVARCHAR(max)') use for ?

The basics of FOR XML PATH you can find in a lot of questions/answers on SO (e.g. 1, or 2).

I'll focus on the TYPE directive. Consider the following T-SQL statement which concatenates the strings in the derived table dt:

SELECT
    [text()]=dt.x+';'
FROM
    (
        VALUES('text > blabla'),
              ('text < blabla'),
              ('f&m')
    ) AS dt(x)
FOR XML
    PATH('');

The result is:

text > blabla;text < blabla;f&m;

You'll see that the >, < and & are substituted by the >, < and &. The special characters (XML predefined entities) will be replaced.

You can disable this by specifying the TYPE directive, which exports this as XML rather than text, then getting the string value from the XML. Getting this string value from the resulting XML, can be done by using the value() method.

Using the TYPE directive in the above example:

SELECT 
    (
        SELECT
            [text()]=dt.x+';'
        FROM
            (
                VALUES('text > blabla'),
                      ('text < blabla'),
                      ('f&m')
            ) AS dt(x)
        FOR XML
            PATH(''), TYPE
    ).value('.','NVARCHAR(MAX)');

You'd get:

text > blabla;text < blabla;f&m;

You can see that the XML special characters are not substituted.