One of the two common string operations that we do often are 'parsing' delimited strings and 'generating' delimited strings. This post explains how to generate a delimited string using FOR XML.

I like XML and many of you might have noted the reflection of this likeness in my posts. When I try to solve a problem, I usually look for an XML based approach before trying any other method. This does not mean that the XML approach is always superior. There are times when it is good and there are times when an XML approach is not desirable.

I have experienced that TSQL loops are very expensive (usually). So most of the times, if you can re-write a loop to a batch/set operation, you could get performance benefits (well, most of the times. There are times when this may not be true, but such cases are very rare).

There are two common string operations where I used to write a TSQL loop in the SQL server 2000 era.

1. To split a delimited string and return a set
2. To generate a delimited string from a set

The XML enhancements added to SQL Server 2005 made both these operations easier with XML. I think, most of the times these operations are done in small pieces of data. Though you can do these operations on extremely large data, I don't think it is advisable. There are other ways to handle large chunks of data.

In this post, lets see how we could generate a delimited string using FOR XML PATH. I have covered the other topic "How to split a delimited string" in another post.

This post is inspired by a discussion with a colleague. Here is the details of the specific requirement. Let us first see the source data.

DECLARE @companies Table(    
    CompanyID INT,    
    CompanyCode  int
) 
    
insert into @companies(CompanyID, CompanyCode) values(1,1)
insert into @companies(CompanyID, CompanyCode) values(1,2)
insert into @companies(CompanyID, CompanyCode) values(2,1)
insert into @companies(CompanyID, CompanyCode) values(2,2)
insert into @companies(CompanyID, CompanyCode) values(2,3)
insert into @companies(CompanyID, CompanyCode) values(2,4)
insert into @companies(CompanyID, CompanyCode) values(3,1)
insert into @companies(CompanyID, CompanyCode) values(3,2) 

SELECT * FROM @companies
/*
CompanyID   CompanyCode
----------- -----------
1           1
1           2
2           1
2           2
2           3
2           4
3           1
3           2
*/

This is the result that we need.

/*
CompanyID   CompanyString
----------- -------------------------
1           1|2
2           1|2|3|4
3           1|2
*/

One option is to run a loop that constructs a delimited string for each CompanyID. Another option is to create a function that returns a delimited string for each company ID. I am presenting a third option using FOR XML PATH.

SELECT CompanyID,
    (SELECT 
        CompanyCode AS 'data()'
     FROM @companies c2 
     WHERE c2.CompanyID = c1.CompanyID
     FOR XML PATH('')) AS CompanyString
FROM @companies c1
GROUP BY CompanyID/*
CompanyID   CompanyString
----------- ------------------------
1           1 2
2           1 2 3 4
3           1 2
*/

The above query uses FOR XML PATH to return a SPACE delimited string containing the company code of each row. But this is not the final result that we need. We need a pipe separated list and hence we need to apply a REPLACE() operation.

SELECT CompanyID,
    REPLACE((SELECT 
        CompanyCode AS 'data()'
     FROM @companies c2 
     WHERE c2.CompanyID = c1.CompanyID
     FOR XML PATH('')), ' ', '|') AS CompanyString
FROM @companies c1
GROUP BY CompanyID

/*
CompanyID   CompanyString
----------- -------------------------
1           1|2
2           1|2|3|4
3           1|2
*/

• How to split a delimited string using XQuery
• FOR XML Tutorials