SQL SERVER
XML

A Complete Guide to the SQL Server FOR XML PATH Clause

intro

Let’s learn everything you need to know about XML serialization in SQL Server through the FOR XML PATH clause.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Interoperability between systems is achieved by having them communicate in the same data format. XML is one of the preferred languages for communication, which is why the ability to seamlessly transform data into XML format has become increasingly crucial. Understanding this need, SQL Server provides a powerful tool: the SQL FOR XML PATH clause.

In this article, you will learn what the SQL FOR XML clause is, how it works, what modes it provides, why FOR XML PATH is the most popular one, and how to use it through some examples.

Let’s dive in!

What Is the SQL FOR XML PATH Clause?

FOR XML PATH is an SQL Server feature to generate XML output directly from a SQL query result. That special T-SQL clause consists of two parts:

Time to learn more about both!

About the FOR XML Clause

FOR XML is an SQL Server clause that allows you to retrieve the formal results of a SQL query as XML. In top-level queries, it can only be used in SELECT statements. When used in SQL subqueries, the clause can also appear in INSERT, UPDATE, and DELETE statements.

The SQL Server FOR XML clause supports four different ways to control the structure and format of the generated XML. These are:

  1. RAW: Generates a single XML <row> element for each row in the result set, without any additional structure.
  2. AUTO: SQL Server attempts to infer the XML structure based on the column names. Each row in the result set becomes an XML element, and each column becomes an attribute or child element of that element. It provides minimal control over the shape of the XML generated.
  3. EXPLICIT: Allows you to specify the structure of the XML explicitly using special column aliases. It gives you more control over the XML tree, but it can become cumbersome.
  4. PATH: Enables you to specify the XML structure using a simpler syntax compared to the EXPLICIT mode. You can specify the path to each element or attribute in the XML hierarchy.

Of these four modes, PATH is by far the most popular because it provides the flexibility of EXPLICIT mode but with a more intuitive syntax. Here is why FOR XML is generally referred to as FOR XML PATH.

SQL Server FOR XML PATH Syntax

Now that you know what the FOR XML clause is, you are ready to explore its syntax:

Copy
        
1 SELECT TableColumns 2 FROM TableName 3 [WHERE ...] 4 [ ... ] 5 FOR XML (RAW | AUTO | EXPLICIT | PATH);

In particular, the syntax of the SQL FOR XML PATH clause is:

Copy
        
1 SELECT TableColumns 2 FROM TableName 3 [WHERE ...] 4 [ ... ] 5 FOR XML PATH;

As you can see, the FOR XML clause should appear at the end of the SQL statement.

Note that PATH supports some options in PATH-like syntax to specify how the XML hierarchy should be generated. Find out more in the official documentation and in the examples below.

FOR XML PATH SQL Example List

See the FOR XML PATH SQL Server clause in action in a few relevant examples.

The sample queries in this section will be applied to the Employees table, which contains this data:

The Data tab of the Employee table in DbVisualizer
The Data tab of the Employee table in DbVisualizer

The Data tab of the Employee table in DbVisualizer

To launch the queries, we will use DbVisualizer—the database client with the highest user satisfaction in the market. Note that any other database client will do.

Time to dig into this FOR XML PATH example list!

Basic Usage

See what happens when applying the SQL FOR XML PATH clause with no options:

Copy
        
1 SELECT TOP 5 Id, Name, Surname 2 FROM Employee 3 FOR XML PATH;

The result of this query will be:

Copy
        
1 <row> 2 <Id>1</Id> 3 <Name>Alice</Name> 4 <Surname>Johnson</Surname> 5 </row> 6 <row> 7 <Id>2</Id> 8 <Name>Charlie</Name> 9 <Surname>Brown</Surname> 10 </row> 11 <row> 12 <Id>3</Id> 13 <Name>Eva</Name> 14 <Surname>Martinez</Surname> 15 </row> 16 <row> 17 <Id>4</Id> 18 <Name>David</Name> 19 <Surname>Miller</Surname> 20 </row>

As you can see, FOR XML PATH transforms each record in the result set in a <row> XML structure containing the selected columns as nested children. Note that this is not a valid XML as there is no root element.

Custom Children

Suppose you now want to customize the XML <row> structures with custom children. All you have to do is specify custom columns in the SELECT clause:

Copy
        
1 SELECT TOP 5 Id, Name, Surname, Name + ' ' + Surname AS CompleteName 2 FROM Employee 3 FOR XML PATH;

The result will be:

Copy
        
1 <row> 2 <Id>1</Id> 3 <Name>Alice</Name> 4 <Surname>Johnson</Surname> 5 <CompleteName>Alice Johnson</CompleteName> 6 </row> 7 <row> 8 <Id>2</Id> 9 <Name>Charlie</Name> 10 <Surname>Brown</Surname> 11 <CompleteName>Charlie Brown</CompleteName> 12 </row> 13 <row> 14 <Id>3</Id> 15 <Name>Eva</Name> 16 <Surname>Martinez</Surname> 17 <CompleteName>Eva Martinez</CompleteName> 18 </row> 19 <row> 20 <Id>4</Id> 21 <Name>David</Name> 22 <Surname>Miller</Surname> 23 <CompleteName>David Miller</CompleteName> 24 </row> 25 <row> 26 <Id>5</Id> 27 <Name>Grace</Name> 28 <Surname>Smith</Surname> 29 <CompleteName>Grace Smith</CompleteName> 30 </row>

Notice the <CompleteName /> child in the <row> elements. Similarly, you can use SQL aliases to customize XML child names.

To customize the XML hierarchy, you can use PATH-like syntax in the PATH argument. For example, use aliases with the / as follows:

Copy
        
1 SELECT TOP 5 2 Id AS 'OriginalData/Id', 3 Name AS 'OriginalData/Name', 4 Surname AS 'OriginalData/Surame', 5 Name + ' ' + Surname AS 'CustomData/CompleteName' 6 FROM 7 Employee 8 FOR XML PATH;

This time, the result will be:

Copy
        
1 <row> 2 <OriginalData> 3 <Id>1</Id> 4 <Name>Alice</Name> 5 <Surame>Johnson</Surame> 6 </OriginalData> 7 <CustomData> 8 <CompleteName>Alice Johnson</CompleteName> 9 </CustomData> 10 </row> 11 <row> 12 <OriginalData> 13 <Id>2</Id> 14 <Name>Charlie</Name> 15 <Surame>Brown</Surame> 16 </OriginalData> 17 <CustomData> 18 <CompleteName>Charlie Brown</CompleteName> 19 </CustomData> 20 </row> 21 <row> 22 <OriginalData> 23 <Id>3</Id> 24 <Name>Eva</Name> 25 <Surame>Martinez</Surame> 26 </OriginalData> 27 <CustomData> 28 <CompleteName>Eva Martinez</CompleteName> 29 </CustomData> 30 </row> 31 <row> 32 <OriginalData> 33 <Id>4</Id> 34 <Name>David</Name> 35 <Surame>Miller</Surame> 36 </OriginalData> 37 <CustomData> 38 <CompleteName>David Miller</CompleteName> 39 </CustomData> 40 </row> 41 <row> 42 <OriginalData> 43 <Id>5</Id> 44 <Name>Grace</Name> 45 <Surame>Smith</Surame> 46 </OriginalData> 47 <CustomData> 48 <CompleteName>Grace Smith</CompleteName> 49 </CustomData> 50 </row>

Custom Row Element Name

row may not be the best name to define your XML records. This is how you can customize the name for your <row> records within an SQL Server FOR XML PATH clause as below (for the purposes of this example, we use Employee as the name):

Copy
        
1 SELECT TOP 5 Id, Name, Surname 2 FROM Employee 3 FOR XML PATH('Employee');

This time, the resulting XML-like output will be:

Copy
        
1 <Employee> 2 <Id>1</Id> 3 <Name>Alice</Name> 4 <Surname>Johnson</Surname> 5 </Employee> 6 <Employee> 7 <Id>2</Id> 8 <Name>Charlie</Name> 9 <Surname>Brown</Surname> 10 </Employee> 11 <Employee> 12 <Id>3</Id> 13 <Name>Eva</Name> 14 <Surname>Martinez</Surname> 15 </Employee> 16 <Employee> 17 <Id>4</Id> 18 <Name>David</Name> 19 <Surname>Miller</Surname> 20 </Employee> 21 <Employee> 22 <Id>5</Id> 23 <Name>Grace</Name> 24 <Surname>Smith</Surname> 25 </Employee>

The default row name is now Employee.

No Row Element

In some situations, you may not even want to specify a <row> element at all. To remove it, use a zero-length string as the custom row name in the FOR XML PATH function:

Copy
        
1 SELECT Id, Name, Surname 2 FROM Employee 3 FOR XML PATH('');

That SQL FOR XML PATH query will produce the following string:

Copy
        
1 <Id>1</Id> 2 <Name>Alice</Name> 3 <Surname>Johnson</Surname> 4 <Id>2</Id> 5 <Name>Charlie</Name> 6 <Surname>Brown</Surname> 7 <Id>3</Id> 8 <Name>Eva</Name> 9 <Surname>Martinez</Surname> 10 <Id>4</Id> 11 <Name>David</Name> 12 <Surname>Miller</Surname> 13 <Id>5</Id> 14 <Name>Grace</Name> 15 <Surname>Smith</Surname>

Note that passing NULL to PATH will result in the following SQL syntax error:

Copy
        
1 Incorrect syntax near the keyword 'NULL'.

This is because the optional argument accepted by PATH must be a string.

Custom Root

All previous FOR XML PATH example queries share the same problem: they do not return a valid XML string. That is because their output does not a valid root element. You can fix that with the ROOT option as below:

Copy
        
1 SELECT TOP 5 Id, Name, Surname 2 FROM Employee 3 FOR XML PATH('Employee'), ROOT('Employees');

Execute this query, and you will get:

Copy
        
1 <Employees> 2 <Employee> 3 <Id>1</Id> 4 <Name>Alice</Name> 5 <Surname>Johnson</Surname> 6 </Employee> 7 <Employee> 8 <Id>2</Id> 9 <Name>Charlie</Name> 10 <Surname>Brown</Surname> 11 </Employee> 12 <Employee> 13 <Id>3</Id> 14 <Name>Eva</Name> 15 <Surname>Martinez</Surname> 16 </Employee> 17 <Employee> 18 <Id>4</Id> 19 <Name>David</Name> 20 <Surname>Miller</Surname> 21 </Employee> 22 <Employee> 23 <Id>5</Id> 24 <Name>Grace</Name> 25 <Surname>Smith</Surname> 26 </Employee> 27 </Employees>

Great, this is valid XML!

If you run this query in DbVisualizer Pro, you will also have the opportunity to use the built-in XML Viewer:

The XML Viewer in action in DbVisualizer Pro
The XML Viewer in action in DbVisualizer Pro

As you can see, this provides XML data exploration capabilities without having to copy the result and paste it into another tool.

Custom XML Attributes

To add an attribute to an XML field, rename a column in SELECT with the following name format:

Copy
        
1 @attribute_name

Take a look at the sample query below:

Copy
        
1 SELECT TOP 5 2 Id AS '@id', 3 Name, 4 Surname 5 FROM 6 Employee 7 FOR XML PATH;

That would produce the XML output below:

Copy
        
1 <row id="1"> 2 <Name>Alice</Name> 3 <Surname>Johnson</Surname> 4 </row> 5 <row id="2"> 6 <Name>Charlie</Name> 7 <Surname>Brown</Surname> 8 </row> 9 <row id="3"> 10 <Name>Eva</Name> 11 <Surname>Martinez</Surname> 12 </row> 13 <row id="4"> 14 <Name>David</Name> 15 <Surname>Miller</Surname> 16 </row> 17 <row id="5"> 18 <Name>Grace</Name> 19 <Surname>Smith</Surname> 20 </row>

As you can see, id is no longer a child element but appears as a top-level XML attribute instead.

String Concatenation

As experienced in the previous examples, the SQL FOR XML PATH clause can be used to get results that are not remotely close to valid XML. In detail, you can take advantage of the feature to go beyond the intended usage and get something completely different out of it.

For example, you can use for string concatenation as follows:

Copy
        
1 SELECT ', ' + Name 2 FROM Employee 3 FOR XML PATH('');

This time, the result will be this string:

Copy
        
1 , Alice, Charlie, Eva, David, Grace, Frank, Helen, George, Ivy, Jack, Karen, Leo

As you can tell, that opens the door to advanced string concatenation queries.

Conclusion

In this guide, you understood what the SQL Server FOR XML PATH clause is and how it works. Now you know that it allows you to convert the result set of a query into an XML-like string. Thanks to the examples shown here, you also learned when and how to use it in real-world examples.

Dealing with XML trees may not be easy, especially when they are particularly nested or long. The ability to visually explore them in the database client would save you a lot of time, which is exactly what the DbVisualizer XML View feature offers.

DbVisualizer is a powerful database client that supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!

FAQ

How to convert SQL records to XML data?

In SQL Server, you can convert SQL records to XML data with the SQL FOR XML clause. Select the right mode (RAW, AUTO, EXPLICIT, or PATH) and specify the required options to generate the desired hierarchy.

What is PATH mode in SQL Server?

In SQL Server, the PATH mode in the FOR XML clause lets you define XML output structures by specifying the hierarchy of elements using a simple syntax.

Does the SQL FOR XML PATH clause support XPath-like column names?

Yes, the FOR XML PATH SQL clause directly supports XPath-like column names. Specifically, it provides the ability to define custom element names for columns in the resulting XML by specifying them as strings within the PATH mode.

What is the difference between a FOR XML PATH SQL Server query and a regular SELECT query?

The main difference between a SQL Server FOR XML PATH query and a regular query is that the former returns a string of XML type, while the latter returns a result set with zero or more records.

What are the performance implications of the SQL Server FOR XML PATH clause?

The SQL FOR XML PATH clause may have performance implications on an SQL Server query due to XML serialization overhead, especially with large datasets. That requires additional processing to generate XML output, potentially impacting query execution time and resource utilization.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

A Complete Guide to the SQL Server COALESCE Function

author Antonello Zanini tags SQL SERVER 6 min 2024-09-23
title

SQL DATEPART: Get a Part of a Date in SQL Server

author Antonello Zanini tags SQL SERVER 8 min 2024-09-19
title

The Definitive Guide to the NULL SQL Server Value

author Antonello Zanini tags SQL SERVER 7 min 2024-09-12
title

Index Creation in SQL

author Lukas Vileikis tags MySQL POSTGRESQL SQL SERVER 7 min 2024-08-15
title

SQL STUFF: Insert a String Into Another in SQL Server

author Antonello Zanini tags SQL SERVER 7 min 2024-07-18
title

SQL Alias: Everything You Need to Know About AS in SQL

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-07-15
title

How to Compare SQL Dates

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 4 min 2024-07-11
title

Primary Key vs. Foreign Key: A Complete Comparison

author Lukas Vileikis tags MySQL POSTGRESQL SQL SQL SERVER 7 min 2024-06-20

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗