The FOR XML clause in SQL Server is used to return query results in XML format. It is useful for applications that require data in XML format.
For example, the following query returns a list of customer names and their respective orders in XML format:
SELECT c.Name, o.OrderNumber
FROM Customers c
INNER JOIN Orders o
ON c.CustomerID = o.CustomerID
FOR XML AUTO, ELEMENTS;
The output of this query would be something like this:
John Smith
12345
Jane Doe
54321