FOR XML Clause - SQL SELECT statement

FOR XML Clause - SQL SELECT statement

SQL SELECT query fetches results as a rowset.  By specifying FOR XML clause in SQL Select query , one can retrieve results as XML instead of rowset. 

In a FOR XML clause, you specify one of these modes:

• RAW - generates a single <row> element per row in the rowset
• AUTO - generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified
• EXPLICIT - allows more control over the shape of the XML
• PATH - together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner

Example :

SQL Query with FOR XML clause with AUTO mode

SELECT TOP 1 EmployeeNumber
FROM Employee Emp
FOR XML AUTO

Query returns results in XML

<Emp EmployeeNumber="100"/>