How to use the FOR XML keywords in SQL to concatenate ids
Part two of a three-part series of blogs

If you want to group items in a SQL query, showing a comma-delimited list of ids for each, generating an XML file is not the obvious place to start - but it should be.

  1. Using FOR XML in SQL to group and concatenate ids
  2. Generating XML from SQL using FOR XML PATH (this blog)
  3. Combining grouping, STUFF and FOR XML in a single query

Posted by Andy Brown on 27 September 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Generating XML from SQL using FOR XML PATH

To explain what FOR XML PATH does, let's start with a basic query.

The basic FOR XML PATH statement

The following query creates basic XML text for Tulisa Bush (person number 1):





tblPerson AS p


p.PersonId = 1


This natty little query produces this:

XML results

The query creates a single output, which is an XML representation of the query results.

I've copied the output to a new XML file (you don't need to do this - it's just to show what XML is) to get:

The XML file

XML is a way of showing relational data in a text format.

There are four keywords you can use after FOR XML (PATH, AUTO, EXPLICIT and RAW), but we just need the first one to get our concatenated ids to work.

Removing clutter from the XML

I'm now going to tweak the query to this:

-- comma-separated ids


',' + CAST(p.CourseId AS varchar(10))


tblPerson AS p


Here's a list of what's changed:

  • I'm showing all the people, not just Tulisa;
  • I'm listing only the course id for each person (with a preceding comma in each case); and
  • I've included ('') after the path, which tells SQL not to generate a root node.

Running this query produces this:

Output of query

We just get the ids, separated by commas.


The XML omits any node names - just listing out the ids - because the column has no alias, and hence no name that SQL can pick up.

Now we've got a way to join the ids together, we need to group them for each person - the subject of the last part of this blog. 

This blog has 0 threads Add post