SQL FOR XML AUTO

An Auto mode with FOR XML in SQL Server returns result set as the nested XML element. Unlike other XML modes, this doesn’t provide much control over the generated XML file. That’s why we don’t use this mode in real-time. In this article we will you list of examples that will explain the use of SQL FOR XML AUTO mode. For this example, We are using the New Employees, Department tables present in the SQL Test. Below screenshot will show you the New Employees table data.

Data present in the Department is as shown below:

SQL FOR XML AUTO Example 1

In this example we will show you the basic way of using FOR XML AUTO mode. The simplest way of using AUTO mode is to append FOR XML AUTO after the Select Statement.

SQL CODE

-- SQL Server FOR XML AUTO Example
USE [SQLTEST]
GO
SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
      ,[DeptID]
  FROM [NewEmployees]
  FOR XML AUTO;

OUTPUT

As you can see from the above screenshot, query has generated the XML file. Please click on the hyperlink to see the XML file.

From the above screenshot you can see that, each row in New Employees table is mapped with <NewEmployees> element, and columns became the attributes. This is fine for the single table, let me try on multiple tables using the Joins.

SQL CODE

-- SQL Server FOR XML AUTO Example
USE [SQLTEST]
GO
SELECT Employee.[EmpID]
      ,Employee.[FirstName]
      ,Employee.[LastName]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML AUTO;

OUTPUT

Now you can see that the row Elements are changed according to the Alias names.

SQL FOR XML AUTO Example 2

From the above screenshot, you can see all the columns are added as the attributes of the <Employee> and <Depart> elements. In this example we will show you, How to add the columns values as the child elements (rather than attributes). In order to achieve the same, we have to use the ELEMENTS keyword along with the FOR XML AUTO.

SQL CODE

-- SQL Server FOR XML AUTO Example
USE [SQLTEST]
GO
SELECT Employee.[EmpID]
      ,Employee.[FirstName]
      ,Employee.[LastName]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML AUTO, ELEMENTS;

OUTPUT

Now you can see that the For XML auto has added the column values as the child elements

SQL FOR XML AUTO Example 3

You can use FOR XML AUTO to create a new root element that will wrap all the existing elements inside it. In order to achieve the same, we have to use the ROOT keyword along with the FOR XML AUTO.

SQL CODE

-- SQL Server FOR XML AUTO Example
USE [SQLTEST]
GO
SELECT Employee.[EmpID]
      ,Employee.[FirstName]
      ,Employee.[LastName]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML AUTO, ROOT, ELEMENTS;

OUTPUT

and the XML file is:

If you observe the above screenshot, there is a <root> element as the parent level. Let me change this default element name.

SQL CODE

-- SQL Server FOR XML AUTO Example
USE [SQLTEST]
GO
SELECT Employee.[EmpID]
      ,Employee.[FirstName]
      ,Employee.[LastName]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML AUTO, ROOT('EmployeDetails'), ELEMENTS;

OUTPUT

Now you can see that the <root> element is replaced by the <EmployeeDetails>

SQL FOR XML AUTO Example 4

If you observe all the above examples, the generated XML file is ignoring the elements with NULL values. This is the default behavior of the For XML Auto but you can change this by adding XSINIL keyword.

SQL CODE

-- SQL Server FOR XML AUTO Example
USE [SQLTEST]
GO
SELECT Employee.[EmpID]
      ,Employee.[FirstName]
      ,Employee.[LastName]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML AUTO, 
          ROOT('EmployeDetails'), 
          ELEMENTS XSINIL;

OUTPUT

Now you can see that the XML file is showing the elements with nulls

Thank You for Visiting Our Blog