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 ?

Possible duplicate of [How Stuff and 'For Xml Path' work in Sql Server](https://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — Ken Y-N, Oct 11 '17 at 08:43
@KenY-N why it was possible if it can execute well on my database ?? — Intan Nur Shafienaz, Oct 11 '17 at 09:34
I just want to ask and get the right information since I am new beginner. — Intan Nur Shafienaz, Oct 11 '17 at 09:35
@ Close voters: Don't know if you noticed, none of the *answers* in the "possible duplicate" explain why the TYPE directive is used when concatenating strings using FOR XML PATH. You guys ought to read the duplicate before smashing that close button. The many down votes are a little exaggerated as well IMO. — TT., Oct 11 '17 at 11:10
Have a look at [thls answer](https://stackoverflow.com/a/14374392/569436) — Mikael Eriksson, Oct 11 '17 at 11:44
@MikaelEriksson That would indeed be a better candidate for a close vote. — TT., Oct 11 '17 at 11:57

TT. · Accepted Answer · 2017-10-11T10:58:48.443

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 &gt; blabla;text &lt; blabla;f&amp;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.

What is exactly XML Path?

1 Answers1