Menu ▼



Posted By :
avatarmrkraju
Total Views : 2250

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

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.

Department tabledata:


Employeestable data:


SQLQuery :

SELECT     tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Salary,

tblDepartments.DepartmentName

FROM         tblEmployees INNERJOIN

tblDepartments ONtblEmployees.DepartmentID = tblDepartments.DepartmentID

Output :


SQLQuery :

SELECT     tblEmployees.EmployeeID, tblEmployees.EmployeeName, tblEmployees.Salary,

tblDepartments.DepartmentName

FROM         tblEmployees INNERJOIN

tblDepartments ON tblEmployees.DepartmentID= tblDepartments.DepartmentID

FOR XML AUTO

Output:

<tblEmployees EmployeeID="1" EmployeeName="Ravi" Salary="15000.00">

  <tblDepartmentsDepartmentName="Accounts" />

</tblEmployees>

<tblEmployees EmployeeID="2" EmployeeName="Steven" Salary="12000.00">

  <tblDepartmentsDepartmentName="Development" />

</tblEmployees>






comments powered by Disqus


Search dotnetlearners.com