Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

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.

SQL FOR XML PATH Example 1

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

SQL FOR XML PATH Example 2

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

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

SQL FOR XML PATH Example 4

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

It is working perfectly with multiple tables

SQL FOR XML PATH Example 6

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

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

SQL FOR XML PATH Example 8

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

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

SQL FOR XML PATH Example 10

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

and the XML file is:

SQL FOR XML PATH Example 12

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

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

and the XML file is:

SQL FOR XML PATH Example 18

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

and the XML file is:

SQL FOR XML PATH Example 24

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

and the XML file is:

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

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

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

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

SQL FOR XML PATH Example 22

Filed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021· All Rights Reserved by Suresh.
About | Contact | Privacy Policy