XML data type was introduced in SQL Server to work with XML data.

Type of XML Data

AUTO

It generates output with both element and attribute features in combination with a sub query.

SELECT * from Employees
FOR XML AUTO; 

EXPLICIT

It converts the rowset that is result of the query execution, into an XML document.

SELECT
 1 tag,
 NULL parent,
 EmpID [employee!1!ID],
 EmpName [employee!1!name],
 NULL [order!2!date],
 NULL [department!3!name]
FROM Employee
UNION ALL 
SELECT
 3,
 1,
 EmpID,
 NULL,
 NULL,
 DeptName
FROM Employee e JOIN Department d
ON e.DeptID=d.DeptID
ORDER BY 3, 1
FOR XML EXPLICIT;

RAW

It produce a single element or the optionally provided element name for each row in the query result set that is returned by select statement.

 SELECT * from Employees
FOR XML RAW;