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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
Thanks for providing good explanation on xml data type.
Can you please explain how to deal with XML For Path type and different methods in XML .
Thanks for explanation on XML Data type.
Please explain on XML FOR Path type and methods in XML Data type.