intro
Let’s learn everything you need to know about XML serialization in SQL Server through the FOR XML PATH clause.
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:
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:
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:
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
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:
1
SELECT TOP 5 Id, Name, Surname
2
FROM Employee
3
FOR XML PATH;
The result of this query will be:
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:
1
SELECT TOP 5 Id, Name, Surname, Name + ' ' + Surname AS CompleteName
2
FROM Employee
3
FOR XML PATH;
The result will be:
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:
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:
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):
1
SELECT TOP 5 Id, Name, Surname
2
FROM Employee
3
FOR XML PATH('Employee');
This time, the resulting XML-like output will be:
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:
1
SELECT Id, Name, Surname
2
FROM Employee
3
FOR XML PATH('');
That SQL FOR XML PATH query will produce the following string:
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:
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:
1
SELECT TOP 5 Id, Name, Surname
2
FROM Employee
3
FOR XML PATH('Employee'), ROOT('Employees');
Execute this query, and you will get:
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:
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:
1
@attribute_name
Take a look at the sample query below:
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:
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:
1
SELECT ', ' + Name
2
FROM Employee
3
FOR XML PATH('');
This time, the result will be this string:
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.
