Just about all of the original XML support first introduced in SQL Server 2000 XML support revolves around FOR XML, a feature that is still very much underused by developers. Since then, SQL Server has enhanced FOR XML in the following ways:
Using the TYPE option, FOR XML can output to an xml data type (as opposed to streamed results) from a SELECT statement, which in turn allows you to nest the results of SELECT…FOR XML into another SELECT statement.
The FOR XML PATH option allows you to more easily shape data and produce element-based XML than the FOR XML EXPLICIT option that we just covered.
You can explicitly specify a ROOT element for your output.
You can produce element-based XML with FOR XML AUTO.
FOR XML can produce ...