SQL Server provides an easy way to getthe result set (data) in XML format by using the inbuilt functionality of FORXML AUTO. Let us discuss the functionality of FOR XML AUTO with anexample.
The following example willget the data from two table tblEmployee and tblDepartment and creates the xml.The output xml in the format EmployeeID,EmployeeName, and Salary are attributesof tblEmployees Node and tblDepartments is the sub node of tblEmployees havingDepartment Name as attribute.
SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Salary,
FROM tblEmployees INNERJOIN
tblDepartments ONtblEmployees.DepartmentID = tblDepartments.DepartmentID
tblDepartments ON tblEmployees.DepartmentID= tblDepartments.DepartmentID
FOR XML AUTO
<tblEmployees EmployeeID="1" EmployeeName="Ravi" Salary="15000.00">
<tblEmployees EmployeeID="2" EmployeeName="Steven" Salary="12000.00">