Rajendra Gupta
ELEMENTS XSINIL clause

FOR XML PATH clause in SQL Server

July 30, 2019 by

As SQL professionals, we often have to deal with XML data in our databases. This article will help you walk through several examples of using ‘FOR XML PATH’ clause in SQL Server.

We get the requirement to display the data from the relational SQL table in various formats. Sometimes developers want to retrieve data in the XML format from the SQL tables holding relational data in regular data types. SQL Server supports XML data using the FOR XML clause. We can easily convert existing data into the XML format using this. We have the following modes available in the FOR XML clause. We can use the FOR XML clause to join or concatenate multiple columns into a single row output as well.

We use the FOR XML PATH SQL Statement to concatenate multiple column data into a single row

  • RAW
  • Auto
  • EXPLICIT
  • PATH

Example 1: Basic use of the FOR XML PATH clause

Let’s use the WideWorldImporters sample database for this part of the article. Execute the following query, and it retrieves the data in a grid format.

Sample data

Click on the Result to Text in the SSMS toolbar and rerun the query.

Result to Text in SSMS toolbar

It gives the same result in a text format. In the following screenshot, we do not see the complete results because you have to scroll the bar in SSMS to see other columns.

Result to Text output

Let’s use the FOR XML PATH clause in previous query and get results in a grid format.

To use Grid format, you can click on Results to Grid in the SSMS menu bar as shown below.

Result to Grid in SSMS toolbar

We get the XML output as a hyperlink.

Output of FOR XML PATH clause

Click on the hyperlink and you get the results in a new window.

XML output

You can note the following in this screenshot.

  • Each row is embedded into <row> and </row> clause
  • In each row, each column value is embedded into <ColumnName> and </ColumnName> clauses

Example 2: Use of an Elements directives with FOR XML PATH

We can use ELEMENTS directives to replace <row> clauses with each row. Previously, we did not specify the ELEMENTS directive, so it returns the default value.

Suppose we want to define root element as Customers. Each row data should be embedded between <CustomerData> and </CustomerData tag>.

In the following query, we added the parameters as following.

  • FOR XML PATH(‘CustomerData’) for each row data
  • ROOT(‘Customers’) for the root tag

Root and Row tags in XML

Example 3: Column Alias in the XML output

Let’s say we want to use CustomerID attribute instead of the <Customerdata> meta data tag. To do so, we can add an alias in the Select statement and use an alias with the @ parameter.

In the following query, We use @CustomerID in the select statement to show the CustomerID as well in the row tag.

Column alias in XML format

Example 4: NULL values and the XML output

Let’s further customized the output using the FOR XML CLAUSE. In the further output, we will use the following data set.

You can get this data using the following query in the AdventureWorks database.

sample data

Let’s view this data in the XML format with the FOR XML PATH clause and alias in the FirstName column.

In this output, You can notice that Kim does not have a middle name therefore, you do not get this column in the XML output.

XML view of the selected data

We can use the ELEMENTS XSINIL parameter to display NULL values as well in the XML output.

It adds the following things in the output.

  • xsi:nil=”true” for the NULL values
  • It also add XML definition in the root element
    <Employee xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”

ELEMENTS XSINIL clause

Example 5: Column Alias on columns other than the individual row tag

Suppose we want to add an alias to columns other than the alias for the individual row tag. In the following query, we defined alias on the middle name and the last name column.

In the following screenshot, we get both the MiddleName and LastName column are enclosed in the <person> tag along with the alias we defined for individual columns.

Column alias and new column

Let’s add a new column in the SELECT statement. We need to be careful in the order of the select statement.

The correct order is to use the new column after the alias columns.

add a new column with the column alias

If we use this new column in between the alias columns, you still get the output, but it might complicate the XML as shown in the following screenshots.

In this screenshot, we get different <person> meta tag for each column with the alias. It might make it complicated to interpret the XML, especially for large data sets.

Incorrect use of adding new column with alias XML

Example 6: Use of the Wildcard character

We can use the wildcard character * with the FOR XML PATH as well. Once we specify a wildcard with a specific column, in the output, we get that column without the column name.

In the following query, we specified the wildcard character for all columns. In the output, we can see it does not return the column name in the XML.

Wildcard character in XML

Let’s remove the wildcard character for the FirstName column.

In the output, you get the column name tag for the Firstname because it does not contain the wildcard character. For other columns, it does not give the column name tag.

Wildcard character in XML

Similarly, in the below query, we use wildcard characters only for the Lastname column.

You can notice the difference in the output. It does not show column name tags only for the LastName column because it contains the wildcard character.

Wildcard character with specified columns  in FOR XML PATH

Example 7: Use of a XMLNAMESPACE for the XML output

We can use XMLNAMESPACE with the FOR XML PATH to declare a namespace and use it in the Select statements. In the following example, we specified XMLNAMESPACE. We call it in the select query.

You get the following output with XMLNAMESPACE query.

 Use of XMLNAMESPACE

Example 8: Create a comma-separated string using the FOR XML PATH clause

We can use FOR XML PATH to prepare a comma-separated string from the existing data. Let’s create an Authors table and insert a few records into it.

In the data, we can see we have an ID column and the AuthorName column. If we just select the records, it gives the output in the following format.

Sample data

Assume, we need to combine the data based on the ID column and display list of authors in a comma-separated view.

We can use FOR XML PAH and get the output using the following query.

Create a comma-separated string using the FOR XML PATH clause

Alternatively, we can use the STUFF function in SQL Server along with the FOR XML PATH to retrieve the same result.

We use the STUFF function to replace a part of a string with substring at a specified position. The STUFF function makes it easy to write the above query and get the same result set.

Conclusion

In this article, we demonstrated FOR XML PATH clause and its usage with different examples. We further learned to customize the XML output. If you have any comments or questions, feel free to leave them in the comments below.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
168 Views