564 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
To explain what FOR XML PATH does, let's start with a basic query.
The following query creates basic XML text for Tulisa Bush (person number 1):
tblPerson AS p
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.
I'm now going to tweak the query to this:
-- comma-separated ids
',' + CAST(p.CourseId AS varchar(10))
tblPerson AS p
FOR XML PATH('')
Here's a list of what's changed:
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|
25 Aytoun Street