Nifty Ways to Use FOR XML PATH Concatenation

In Inside Microsoft SQL Server 2005: T-SQL Programming (Microsoft Press, 2006) by Itzik Ben-Gan, Dejan Sarka, and Roger Wolter, I was delighted to read about a clever way of using SQL Server 2005’s FOR XML PATH option to concatenate string values in rows into a single column or result set without using temporary tables or table variables (see page 109). This unorthodox method of concatenating string values can be quite useful in queries. For example, you can use it to list all columns in an index or all files in a file group.

See also: How do I retrieve values from a table and store the results in a string?

Listing 1 shows a traditional query to list all the columns in the indexes for a table or view. In the results in Figure 1, notice that the index IX_Address_AddressLine1_Address Line2_City_StateProvince_PostalCode_Country Region (index_id 3) has six columns: AddressLine1, AddressLine2, City, StateProvince, PostalCode, and CountryRegion.

You can use the FOR XML PATH option and the text() XPath node test (see SQL Server 2005 Books Online—BOL at msdn.microsoft.com/en-us/library/ms345115.aspx) to list all the indexes’ columns in a single concatenated string, as shown in Listing 2. Figure 2 shows the results from this query. As you can see, a comma is prepended to each column name. Granted, having all the column names listed together like this is of limited use.

However, you can refine this technique so that it produces useable results. By using the handy STUFF string function with the FOR XML PATH option, you can get output like that in Figure 3, page 10.

In Figure 3, notice the row for the multi-column index IX_Address_AddressLine1_AddressLine2_City_StateProvince_PostalCode_CountryRegion. The AddressLine1, AddressLine2, City, StateProvince, PostalCode, and CountryRegion columns are all now in this row, with their names separated by a comma. Listing 3, page 10, shows the query that was used to produce this result. In this query, the STUFF function removes the comma preceding the first name in the comma-separated column list.

The query in Listing 3 is only meant to demonstrate how you can concatenate the names of columns inmulticolumn indexes. A query that you can use on your databases to list all the columns in every index is available online in the file named FullQuery.sql. You can download this file by going to www.sqlmag.com, entering 99197 in the InstantDoc ID text box, clicking Go, then clicking the 99197.zip hotlink.

You can use the FOR XML PATH option with the STUFF function for other tasks. For example, you can use this technique to concatenate the names of files in a file group, as shown in Listing 4, page 10. In addition to the FOR XML PATH option and STUFF function, this query uses a derived table and the dynamic management view (DMV) dm_io_virtual_file_stats to summarize statistics for all the databases and all the files given that NULL, NULL parameters are passed to dm_io_virtual_file_stats. The DMV provides detailed information for each file in the database. The query in Listing 4 groups the results by database, as Figure 4 shows. This can be handy for looking at databases from a high-level perspective so that, for example, you can easily determine which database has the highest reads. You’d just need to sort the results by descending DB_Reads.

Note that no temporary tables or table variables were needed to perform these nifty concatenation operations. As Inside Microsoft SQL Server 2005: T-SQL Programming mentions, not only is the resulting query “short and slick” but it is also high performing. With these examples as a starting point, can you think of other creative ways to use FOR XML PATH to display concatenated string results?

See also: UDF Eliminates Concatenation Woes

—Raul Pubill, senior database administrator, JP Morgan FCS