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 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 and Department tables present in the 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

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

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.

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

It works perfectly with multiple tables

SQL FOR XML PATH Example 2

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

-- 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 PATH, ELEMENTS;

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

SQL FOR XML PATH Example with root

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 a custom element.

-- Example

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>

SQL FOR XML PATH Example 4

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

-- SQL Server FOR XML PATH Example

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:

SQL FOR XML PATH Example 5

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

-- SQL Server FOR XML PATH Example

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 add as an attribute to the row element.

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

-- SQL Server FOR XML PATH Example

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 start 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 is created, and both First and Last are added as an attribute to the FullName.

-- SQL Server FOR XML PATH Example

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:

Example 8

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

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.

-- Example

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

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 the XML file. In this example, we will show the same.

-- SQL Server FOR XML PATH Example

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