Understanding how FOR XML PATH used to generate comma seperated list

08 Mar

In SQL server you might have a parent table which have set of rows which have related data in a child table which have 1 to many relationship in between parent and child rows so in your queries you might need to generate comma separated list of child data for each row that of parent table. You can do this with a loop or with cursor but there is much efficient way to do it with FOR XML PATH in SQL server.

consider following query where you need to get comma separated list of accommodations in each accommodation group.

SELECT  ag.AccGrpID ,
( SELECT    NAME + ','
FROM      dbo.Accommodation ac
WHERE     ac.AccGrpID = ag.accgrpid
) AS CommaSeperatedAccommodations
FROM    dbo.AccommodationGroup ag

But what is happening here? when you run a FOR XML PATH query against set of data in SSMS it will Return scalar value which contains XML (of course this is XML data type representation). Try converting it in to NVARCHAR(4000) or NVARCHAR(MAX) you get result set without XML element names. This conversion is implicitly happen when you run above query because when you do a + ‘,’ in code XML data get converted to NVARCHAR resulting comma separated list. This list will return a comma separated list with additional trailing comma but you can easily remove it with string manipulation function in TSQL.

Leave a comment

Posted by on March 8, 2012 in SQL



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: