SQL FOR XML PATH

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

In this article we will show you list of examples that explains the use of SQL FOR XML PATH mode. For this example, We are using the New Employees, Department tables present in the SQL Test Database. Below screenshot will show you the New Employees table data.

Data present in the Department is as shown below:

SQL FOR XML PATH Example 1

This example show 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 CODE

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

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.

This is fine for the single table, let me try on multiple tables using the Joins.

SQL CODE

-- 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

OUTPUT

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 CODE

-- 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;

OUTPUT

As you can see there is no difference in the output. Because, Path mode is already performing this operation

SQL FOR XML PATH Example 3

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

SQL CODE

-- 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')

OUTPUT

As you can see from the below screenshot, <row> element is replaced with the <Employees>

SQL 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. In order to achieve the same, we have to use the ROOT keyword along with the FOR XML PATH.

SQL CODE

-- 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;

OUTPUT

and the XML file is:

SQL 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 CODE

-- 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')

OUTPUT

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 will be added as an attribute to the Employees row.

SQL CODE

-- 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');

OUTPUT

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 sql for xml path code snippet, First, and Last are not starting with @, but it contains slash symbol. It means, new hierarchy called FullName will be created and both First, and Last will be added as an attribute to the FullName.

SQL CODE

-- 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');

OUTPUT

and the XML file is:

SQL FOR XML PATH Example 8

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

SQL CODE

-- 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;

OUTPUT

and the XML file is:

SQL FOR XML PATH Example 9

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

SQL CODE

-- 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;

OUTPUT

and the XML file is:

SQL FOR XML PATH Example 10

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

SQL CODE

-- 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;

OUTPUT

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

SQL 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 CODE

-- 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;

OUTPUT

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

Thank You for Visiting Our Blog