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.

Source Table 1

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

Department Table 2

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;
SQL FOR XML PATH Example 3

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

generated File 4

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

It works perfectly with multiple tables

Generated Multiple Files Example 6

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

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

Path Mode 8

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 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')
SQL FOR XML PATH Example 9

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

Changing or Updating Nodes 10

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;
SQL FOR XML PATH Example 11

and the XML file is:

SQL FOR XML PATH Example 12

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')
SQL FOR XML PATH Example 13

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

SQL FOR XML PATH Example 14

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

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');
SQL FOR XML PATH Example 15

and the XML file is:

SQL FOR XML PATH Example 16

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');
SQL FOR XML PATH Example 17

and the XML file is:

SQL FOR XML PATH Example 18

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;
SQL FOR XML PATH Example 23

and the XML file is:

View File 24

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;
SQL FOR XML PATH Example 25

and the XML file is:

View File 26

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;
SQL FOR XML PATH Example 19

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

XML File With Null Values from Table 20

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

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;
SQL FOR XML PATH Example 21

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

SQL FOR XML PATH Example 22

Comments are closed.