SQL FOR XML PATH

The Path mode with FOR XML in SQL Server returns a result set as the XML element. Unlike other XML modes, this SQL FOR XML PATH mode provides control over the generated XML file. It is because FOR XML path mode treats column names and alias names as the XPath expression.

For this SQL FOR XML PATH mode example, We are using the New Employees, Department tables present in the SQL Test Database. The below screenshot will show you the New Employees table data.

Data present in the SQL Server Department in our database is as shown below:

SQL FOR XML PATH Example 1

This example shows you the basic way of using FOR XML PATH mode. The simplest way of using path mode is to append FOR XML PATH after the Select Statement.

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

The above query has generated the XML file. Please click on the hyperlink to see the XML file.

It is okay for the single table, let me try on multiple tables using the Joins.

-- SQL Server FOR XML PATH 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 PATH

It is working perfectly with multiple tables

SQL FOR XML PATH Example 2

The SQL Server provides ELEMENTS keyword to display the column names as nested elements. Let me use this keyword along with the FOR XML PATH.

-- SQL Server FOR XML PATH 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 PATH, ELEMENTS;

See, there is no difference in the output. Because Path mode is already performing this operation

FOR XML PATH Example 3

If you observe the above screenshot, every row separated by the default <row> element. In this example, we will show you how to override the default row with a custom element.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT 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 PATH('Employees')

From the below screenshot, <row> element is replaced with the <Employees>

FOR XML PATH Example 4

The SQL Server FOR XML PATH allows you to create a new root element that will wrap all the existing elements inside it. To achieve the same, we have to use the ROOT keyword along with the FOR XML PATH.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT 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 PATH('Employees'), ROOT;

and the XML file is:

FOR XML PATH Example 5

If you observe the above screenshot, there is a <root> element as the parent level. In this sql for xml path example we will change this default element name.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT 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 PATH('Employees'), ROOT('EmployeeDetails')

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

SQL FOR XML PATH Example 6

If a column name (or Alias name) starts with @ symbol, and does not contain the / symbol then that column will be added as an attribute to the row element.

From the below SQL Server for XML path code snippet, First, and Last are starting with @. It means both of them added as an attribute to the Employees row.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT Employee.[FirstName] AS [@First]
      ,Employee.[LastName] AS [@Last]
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails');

and the XML file is:

SQL FOR XML PATH Example 7

If a column name (or Alias name) does not starts with @ symbol, but contains the / symbol then that column name indicates hierarchy.

From the below for XML path code snippet, First and Last are not starting with @, but it contains a slash symbol. It means a new hierarchy called FullName created, and both First and Last added as an attribute to the FullName.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT Employee.[FirstName] AS 'FullName/@First'
      ,Employee.[LastName] AS 'FullName/@Last'
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails');

and the XML file is:

FOR XML PATH Example 8

You can also use the Wildcards along with the Sql Server For XML PATH.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT Employee.[EmpID] AS [@EmployeeID]
      ,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 PATH('Employees'), 
          ROOT('EmployeeDetails'),
	  ELEMENTS XSINIL;

and the XML file is:

FOR XML PATH Example 9

In this example ,we will show you, How the Sql Server XML Path handles the unnamed columns.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT Employee.[Education]
      ,SUM(Employee.[YearlyIncome])
  FROM [NewEmployees] AS Employee
  GROUP BY 	Employee.[Education]
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails'),
		  ELEMENTS XSINIL;

and the XML file is:

FOR XML PATH Example 10

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

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
SELECT Employee.[FirstName] AS 'Name/@First'
      ,Employee.[LastName] AS 'Name/@Last'
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails'),
	  ELEMENTS XSINIL;

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

FOR XML PATH Example 11

By using the XMLNAMESPACES keyword along with SQL For XML Path, you can assign your custom namespace to XML file. In this example, we will show the same.

-- SQL Server FOR XML PATH Example
USE [SQLTEST]
GO
WITH XMLNAMESPACES('tutorialgateway.org' as TutorialGateway)  
SELECT Employee.[FirstName] AS 'Name/@First'
      ,Employee.[LastName] AS 'Name/@Last'
      ,Employee.[Education]
      ,Employee.[YearlyIncome]
      ,Employee.[Sales]
      ,Depart.[DepartmentName]
  FROM [NewEmployees] AS Employee
  INNER JOIN [Department] AS Depart 
  ON Employee.DeptID = Depart.DeptID
  FOR XML PATH('Employees'), 
          ROOT('EmployeeDetails'),
	  ELEMENTS XSINIL;

You can see the namespace that we used in the above query