Pages

Thursday, July 2, 2009

FOR XML PATH - How to generate a Delimited String using FOR XML PATH?

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.

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,
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

The above query uses FOR XML PATH to return a comma delimited string containing the company
code of each row.
*/

0 comments:

Post a Comment