If you read SQL forums as regularly as I do (yeah, I don’t have a life, don’t rub it in), you will find that people often ask about how to generate a concatenated string using values in a column of a table. A simple example might be like this
Item Name Apple Banana Orange
Given this, you want to get the output as “Apple, Banana, Orange”. Question is, how do you do it. And, the answer is to Google for it and find the 14,322 blogs that give you a short and sweet script that does exactly that. In case you read through all those blogs and still are eager to find what yours truly has to say about it, you have come to the right place.
Before I plagiarize the solution from one of those other shallow blogs, I want to tell you that I am going to use the “FOR XML PATH(”)” construct to do this. The important thing to realize about that construct is that it is really meant for generating an XML document from data stored in relational tables. The following example generates an XML document out of the list of presidents stored in a relational table.
CREATE TABLE #Presidents (id INT, lastname VARCHAR(32), firstname VARCHAR(32)); INSERT INTO #Presidents VALUES (1,'Washington','George'), (2,'Adams','John'), (3,'Jefferson','Thomas'), (4,'Madison','James'); SELECT id,lastname,firstname FROM #Presidents FOR XML PATH('President'),ROOT('Presidents'); DROP TABLE #Presidents;
--- You get this result <Presidents> <President> <id>1</id> <lastname>Washington</lastname> <firstname>George</firstname> </President> <President> <id>2</id> <lastname>Adams</lastname> <firstname>John</firstname> </President> <President> <id>3</id> <lastname>Jefferson</lastname> <firstname>Thomas</firstname> </President> <President> <id>4</id> <lastname>Madison</lastname> <firstname>James</firstname> </President> </Presidents>
We are going to (or rather I am going to steal from somewhere) a way of twisting the XML PATH construct to generate a comma-separated list.
-- create a temp table with sample data. CREATE TABLE #tmp (Item VARCHAR(32)); INSERT INTO #tmp VALUES( 'Apple'),('Banana'), ('Orange'); -- concatenate SELECT ','+Item FROM #tmp FOR XML PATH(''); -- clean up the mess -- DROP TABLE #tmp; -- this is the result I get -- ,Apple,Banana,Orange
Wasn’t that painless? All you had to do was to add that FOR XML PATH(”). Of course, there is an extra comma at the beginning that we need to take care of, but we will defer that for later.
So now, being the smart, intelligent, curious person that you are, you are probably asking yourself “But how does it work? And why does it work?” If you are going to ask such difficult questions, you are reading the wrong blog. Go read something that is not this irreverant and shallow!! Google for Jacob Sebastian’s XML lab. You will get a lot out of his articles.
Ok, so now let us do an experiment. Remove that leading comma from the select statement..
SELECT Item FROM #tmp FOR XML PATH(''); -- you get something like this! -- <Item>Apple</Item><Item>Banana</Item><Item>Orange</Item>
Holy Mary Mother of God, did that comma make all that difference? and what is with this “Item” tags!!
As you might have guessed, it is not that comma is special (no offense meant to you Miss comma), it is that when you had the comma in there, the resulting column in your SELECT did not have a column name, and so SQL XML did not know what tags to assign – and so it assigned none. If you are not convinced, try the following two or three experiments:..
SELECT ','+Item as NewItem FROM #tmp FOR XML PATH(''); -- you get this -- <NewItem>,Apple</NewItem><NewItem>,Banana</NewItem><NewItem>,Orange</NewItem> SELECT Item as [text()] FROM #tmp FOR XML PATH(''); -- you get this -- AppleBananaOrange SELECT Item as [data()] FROM #tmp FOR XML PATH(''); -- you get this (Notice the space between each item) -- Apple Banana Orange
So, the trick is to structure your SELECT such that the columns in the SELECT list do not have column names. If they do, SQL XML will arrogate that name and use that as the node name for the XML.
There! Now you know how we trick SQL XML into generating a non-XML-XML aka string concatenation.
Now if you have nothing better to do, try to do a few experiments:
1. Insert something between the single quotes in that XML PAHT(”) and see what you get.
2. create a table with two columns (for example, Item and ItemCategory) and try to concatenate all of that together. (Of course, I know how to do that. I may be shallow, but I do know THAT).
If you are curious about what those text() and data() functions that I used are, go read these MSDN pages:
There may be better explanations on other MSDN pages, my battery is dead at this point, so you are on your own to find those.