SQL Query to get result set in XML format using FOR XML AUTO

SQL Server provides an easy way to get the result set (data) in XML format by using the inbuilt functionality of FOR XML AUTO. Let us discuss the functionality of FOR XML AUTO with an example.

The following example will get the data from two table tblEmployee and tblDepartment and creates the xml. The output xml in the format EmployeeID,EmployeeName, and Salary are attributes of tblEmployees Node and tblDepartments is the sub node of tblEmployees having Department Name as attribute.


Department table data:



SQL Query:

SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Salary, tblDepartments.DepartmentName FROM tblEmployees INNER JOIN tblDepartments ON tblEmployees.DepartmentID = tblDepartments.DepartmentID

Output:


SQL Query:

SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Salary, tblDepartments.DepartmentName FROM tblEmployees INNER JOIN tblDepartments ON tblEmployees.DepartmentID = tblDepartments.DepartmentID FOR XML AUTO

Output:

<tblEmployees EmployeeID="1" EmployeeName="Ravi" Salary="15000.00"> <tblDepartments DepartmentName="Accounts" /> </tblEmployees> <tblEmployees EmployeeID="2" EmployeeName="Steven" Salary="12000.00"> <tblDepartments DepartmentName="Development" /> </tblEmployees>