SQL FOR XML AUTO

The FOR XML Auto mode in SQL Server returns a result set as the nested XML element. Unlike other XML modes, For XML AUTO doesn’t provide much control over the generated XML file. That’s why we don’t use this SQL FOR XML AUTO mode in real-time.

For this SQL FOR XML AUTO example, we are using the New Employees, Department tables present in the SQL Test. The below screenshot will show you the New Employees table data in SQL Server.

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 SQL FOR XML AUTO mode. The simplest way of using AUTO mode is to append FOR XML AUTO after the Select Statement.

-- SQL Server FOR XML AUTO Example

SELECT  [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[YearlyIncome]
      ,[Sales]
      ,[DeptID]
  FROM [NewEmployees]
  FOR XML AUTO;

As you can see, 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 the New Employees table mapped with <NewEmployees> element, and columns became the attributes. It is excellent for the single table, let me try on multiple SQL Server tables using the Joins.

-- SQL Server FOR XML AUTO Example
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;

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

SQL FOR XML AUTO Example 2

As you can see, all the columns added as the attributes of the <Employee> and <Depart> elements. In this example, we will show you how to add the column values as the child elements (rather than attributes). To achieve the same, we use the ELEMENTS keyword along with the FOR XML AUTO in SQL Server.

-- SQL Server FOR XML AUTO Example
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;

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

FOR XML AUTO Example 3

You can use SQL Server FOR XML AUTO to create a new root element that will wrap all the existing elements inside it. To accomplish the same, we must use the ROOT keyword along with the FOR XML AUTO.

-- SQL Server FOR XML AUTO Example

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;

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 Server FOR XML AUTO Example
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;

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

FOR XML AUTO Example 4

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

-- SQL Server FOR XML AUTO Example
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;

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