Using the FOR XML Clause to Return Query Results as XML

SQL Server lets you retrieve data as XML by supporting the FOR XML clause, which can be included as part of your query. You can use the FOR XML clause in the main (outer) query as well as in subqueries. The clause supports numerous options that let you define the format of the XML data.

When you include the FOR XML clause in your query, you must specify one of the four supported modes—RAW, AUTO, EXPLICIT, or PATH. The options available to each mode vary according to that mode; however, many of the options are shared among the modes. In this article, I explain how to use each of these modes to retrieve data as XML and provide examples that demonstrate how they use the various options.

The RAW Mode

The RAW mode generates a single XML element for each row in the result set returned by the query.

To use the FOR XML clause in RAW mode, you simply append the clause and RAW keyword to your SELECT statement, as shown in the following example:

it return row's in a form of xml.

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'Rob'
FOR XML RAW;

row's get the id basix (ie name)

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'Rob'
FOR XML RAW ('Employee');

can specify only roots/ elements to

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'Rob'
FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS;

no elements are created for a column whose value is null. However, you can override this behavior by adding the XSINIL keyword to the ELEMENTS option:

SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName
FROM HumanResources.Employee e INNER JOIN Person.Contact c
ON c.ContactID = e.ContactID
WHERE c.FirstName = 'Rob'
FOR XML RAW ('Employee'), ROOT ('Employees'), ELEMENTS XSINIL;