This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Using FOR XML in SQL to group and concatenate ids Generating XML from SQL using FOR XML PATH (this blog) Combining grouping, STUFF and FOR XML in a single query

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.

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

Posted by Andy Brown on 27 September 2016

In this blog

The basic FOR XML PATH statement
Removing clutter from the XML

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):

SELECT

p.PersonName,

p.CourseId

FROM

tblPerson AS p

WHERE

p.PersonId = 1

FOR XML PATH

This natty little query produces this:

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:

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

SELECT

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

FROM

tblPerson AS p

FOR XML PATH('')

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:

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.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post