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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 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
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 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 example we will change this default element name.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 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 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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 XML PATH.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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 XML Path handles the unnamed columns.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 |
-- 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 behavior of the For XML Path but you can change this by adding ELEMENTS XSINIL keyword.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- 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 you can assign your custom namespace to XML file. In this example, we will show the same.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- 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
Great examples – nicely done!
Terrific! Thank you so much!