How Stuff and 'For Xml Path' work in SQL Server?
Solution 1
Here is how it works:
1. Get XML element string with FOR XML
Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement:
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
By passing in a blank string (FOR XML PATH('')), we get the following instead:
,aaa,bbb,ccc,ddd,eee
2. Remove leading comma with STUFF
The STUFF statement literally "stuffs” one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
The parameters of STUFF
are:
- The string to be “stuffed” (in our case the full list of name with a leading comma)
- The location to start deleting and inserting characters (1, we’re stuffing into a blank string)
- The number of characters to delete (1, being the leading comma)
So we end up with:
aaa,bbb,ccc,ddd,eee
3. Join on id to get full list
Next we just join this on the list of id in the temp table, to get a list of IDs with name:
SELECT ID, abc = STUFF(
(SELECT ',' + name
FROM temp1 t1
WHERE t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') from temp1 t2
group by id;
And we have our result:
Id | Name |
---|---|
1 | aaa,bbb,ccc,ddd,eee |
Solution 2
This article covers various ways of concatenating strings in SQL, including an improved version of your code which doesn't XML-encode the concatenated values.
SELECT ID, abc = STUFF
(
(
SELECT ',' + name
FROM temp1 As T2
-- You only want to combine rows for a single ID here:
WHERE T2.ID = T1.ID
ORDER BY name
FOR XML PATH (''), TYPE
).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id
To understand what's happening, start with the inner query:
SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE
Because you're specifying FOR XML
, you'll get a single row containing an XML fragment representing all of the rows.
Because you haven't specified a column alias for the first column, each row would be wrapped in an XML element with the name specified in brackets after the FOR XML PATH
. For example, if you had FOR XML PATH ('X')
, you'd get an XML document that looked like:
<X>,aaa</X>
<X>,bbb</X>
...
But, since you haven't specified an element name, you just get a list of values:
,aaa,bbb,...
The .value('.', 'varchar(max)')
simply retrieves the value from the resulting XML fragment, without XML-encoding any "special" characters. You now have a string that looks like:
',aaa,bbb,...'
The STUFF
function then removes the leading comma, giving you a final result that looks like:
'aaa,bbb,...'
It looks quite confusing at first glance, but it does tend to perform quite well compared to some of the other options.
Solution 3
PATH mode is used in generating XML from a SELECT query
1. SELECT
ID,
Name
FROM temp1
FOR XML PATH;
Ouput:
<row>
<ID>1</ID>
<Name>aaa</Name>
</row>
<row>
<ID>1</ID>
<Name>bbb</Name>
</row>
<row>
<ID>1</ID>
<Name>ccc</Name>
</row>
<row>
<ID>1</ID>
<Name>ddd</Name>
</row>
<row>
<ID>1</ID>
<Name>eee</Name>
</row>
The Output is element-centric XML where each column value in the resulting rowset is wrapped in an row element. Because the SELECT clause does not specify any aliases for the column names, the child element names generated are the same as the corresponding column names in the SELECT clause.
For each row in the rowset a tag is added.
2.
SELECT
ID,
Name
FROM temp1
FOR XML PATH('');
Ouput:
<ID>1</ID>
<Name>aaa</Name>
<ID>1</ID>
<Name>bbb</Name>
<ID>1</ID>
<Name>ccc</Name>
<ID>1</ID>
<Name>ddd</Name>
<ID>1</ID>
<Name>eee</Name>
For Step 2: If you specify a zero-length string, the wrapping element is not produced.
3.
SELECT
Name
FROM temp1
FOR XML PATH('');
Ouput:
<Name>aaa</Name>
<Name>bbb</Name>
<Name>ccc</Name>
<Name>ddd</Name>
<Name>eee</Name>
4. SELECT
',' +Name
FROM temp1
FOR XML PATH('')
Ouput:
,aaa,bbb,ccc,ddd,eee
In Step 4 we are concatenating the values.
5. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1
Ouput:
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
1 ,aaa,bbb,ccc,ddd,eee
6. SELECT ID,
abc = (SELECT
',' +Name
FROM temp1
FOR XML PATH('') )
FROM temp1 GROUP by iD
Ouput:
ID abc
1 ,aaa,bbb,ccc,ddd,eee
In Step 6 we are grouping the date by ID.
STUFF( source_string, start, length, add_string ) Parameters or Arguments source_string The source string to modify. start The position in the source_string to delete length characters and then insert add_string. length The number of characters to delete from source_string. add_string The sequence of characters to insert into the source_string at the start position.
SELECT ID,
abc =
STUFF (
(SELECT
',' +Name
FROM temp1
FOR XML PATH('')), 1, 1, ''
)
FROM temp1 GROUP by iD
Output:
-----------------------------------
| Id | Name |
|---------------------------------|
| 1 | aaa,bbb,ccc,ddd,eee |
-----------------------------------
Solution 4
There is very new functionality in Azure SQL Database and SQL Server (starting with 2017) to handle this exact scenario. I believe this would serve as a native official method for what you are trying to accomplish with the XML/STUFF method. Example:
select id, STRING_AGG(name, ',') as abc
from temp1
group by id
STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx
EDIT: When I originally posted this I made mention of SQL Server 2016 as I thought I saw that on a potential feature that was to be included. Either I remembered that incorrectly or something changed, thanks for the suggested edit fixing the version. Also, pretty impressed and wasn't fully aware of the multi-step review process that just pulled me in for a final option.
Solution 5
In for xml path
, if we define any value like [ for xml path('ENVLOPE') ]
then these tags will be added with each row:
<ENVLOPE>
</ENVLOPE>
![Puneet Chawla](https://i.stack.imgur.com/5xSIE.jpg?s=256&g=1)
Puneet Chawla
Having experience in the field of Website Development and Designing and recently working as a "Software Developer" at Imbibe Technologies Private Limited. Provided training one and half year as a "Technical Trainer" at MDA ITHub InfoTech Private Limited in these below technologies. HTML, CSS, JavaScript, C#, Java (Core Java, JEE) and MS SQL Server. Worked as as "Software Engineer" at Imbibe Technologies in these below technologies. HTML, CSS, ExtJS, ASP.NET, C#, MVC, Entity Framework and MS SQL Server.
Updated on December 01, 2021Comments
-
Puneet Chawla over 2 years
Table is:
Id Name 1 aaa 1 bbb 1 ccc 1 ddd 1 eee Required output:
Id abc 1 aaa,bbb,ccc,ddd,eee Query:
SELECT ID, abc = STUFF( (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, '' ) FROM temp1 GROUP BY id
This query is working properly. But I just need the explanation how it works or is there any other or short way to do this.
I am getting very confused to understand this.
-
Puneet Chawla almost 9 yearsHi Richard, Thanks for answer. It's little bit confusing but i almost got complete explanation and logic.
-
Puneet Chawla almost 9 yearsWhat's the use of Type in your query. I think it for defining, the result of XML path will be store in value (not sure explain it if wrong).
-
Richard Deeming almost 9 years@PuneetChawla: The
TYPE
directive tells SQL to return the data using thexml
type. Without it, the data is returned as annvarchar(max)
. It's used here to avoid XML-encoding issues if there are special characters in thename
column. -
N8allan about 7 yearsSTRING_AGG is not in SQL Server 2016. It is said to be coming in "vNext".
-
Fandango68 about 7 yearsYou should work for Microsoft's documentation team (if any)
-
Brian Jorden over 6 yearsOops, I did not mean to overwrite the edit from @lostmylogin sorry about that... That is who actually pushed through the correction edit.
-
Chris over 6 years@Fandango68 ,@FutbolFan - He can't work for Microsoft's documentation team. His explanations are too clear and too direct. ;-)
-
user3790083 over 6 yearsAdding STUFF and type/.value together for me had a big impact on query. i had Table valued function XML reader being costly. I left STUFF only and works for my case.
-
barlop about 6 yearsYou write "In Step 4 we are concatenating the values." But it's not clear why/how the
','
specified as column, combined with the('')
after xml path, causes concatenation to occur -
barlop about 6 yearsI notice that if you delete the parameter
TYPE
and you delete.value('.', 'varchar(max)')
then it works the same. TheType
seems to convert it into an XML data type, and the.value('.', 'varchar(max)')
converts it back out. So you can remove both. (i'm no expert though.. maybe there is some advantage in including those two things?) -
Richard Deeming about 6 years@barlop: As the SimpleTalk article explains, if you drop the
TYPE
and.value('.', 'varchar(max)')
, then you can end up with XML-encoded entities in the result. -
barlop about 6 years@RichardDeeming do you mean if the data contains or might contain angle brackets?
-
Richard Deeming about 6 years@barlop: Angle brackets, ampersands, quotes, apostrophes, or anything else that needs to be encoded for use in XML.
-
vCillusion about 6 yearsIn Step 4, doing any string operation will use the specified wrapping element which is blank ('') for this case.
-
imma about 6 yearsjust had a problem where 'item 1 & item 2' was coming out as 'item 1 & item 2' - this answer fixes it (with the TYPE and .value...), thankyou
-
ChrisProsser over 5 yearsGood answer. I am surprised that Microsoft didn't make a more convenient way to combine strings until the String_Agg function added in 2017 (stackoverflow.com/a/42967358/2012446). I find stuff / xml path a very verbose/cumbersome way to do this.
-
FutbolFan over 5 years@ChrisProsser I agree. Oracle has been ahead of Microsoft on this by introducing
LISTAGG
function in Oracle 11gR2. I do miss that functionality on days where I have to use this instead. techonthenet.com/oracle/functions/listagg.php -
KevinVictor over 5 yearsHello. In step 1, if you do: SELECT name FROM temp1 FOR XML PATH ('') ...you get <name>aaa</name><name>bbb</name> ...etc... I didn't realize this at first... Changing it to SELECT ''+name ...etc... removes the tags.
-
youcantryreachingme over 5 years@ChrisProsser - Sybase ASA has had a
list
function for decades. Unfortunately Microsoft based SQLServer on Sybase's ASE instead, and never bothered with a list function until last year. I agree - it's mind-boggling. And then they do, they call itstring_agg
. I'd have thoughtlist
was pretty obvious. -
youcantryreachingme over 5 years@ChrisProsser but then, in Sybase ASA you can reference column aliases in
where
clauses without the convoluted syntax of CTEs in spite of MS insisting it is not possible due to order of evaluation operations. But I digress. -
anth over 5 yearsFor anyone wondering about point 4 and why <Name> dissapears. It's because after concatenation Name with comma there is no longer column but just value, so SQL Server doesn't know what name for xml tag should be used. For example this query
SELECT 'a' FROM some_table FOR XML PATH('')
will produce:'aaaaaaa'
. But if column name will be specified:SELECT 'a' AS Col FROM some_table FOR XML PATH('')
you get result:<Col>a</Col><Col>a</Col><Col>a</Col>
-
JSONaLeo over 5 yearsI just want to say, I've referenced this answer probably like 15 times now, and finally just copied the example into my snips. Thanks @FutbolFan
-
Neil Walker over 5 yearsJust wondering where it was obvious that adding generated calculated fields (rather than normal fields) creates no xml wrapper around the value...
-
Adam over 4 yearsBut, since you haven't specified an element name, you just get a list of values, this is the insight I was missing. Thank you.
-
Admin about 4 years@KevinVictor, the For XML Path will actually put the name of the column before the value. Adding a ''+Name or doing a Convert(varchar(x),name) cause the column to have no name (you can test it in SSMS easilly by running just the select) and this is why you don't have it in the xml string. in this specific case, you also want the "," to be inserted between the elements so that 1 stone 2 birds.
-
Ferdinand Gaspar almost 4 yearsvarchar(max) truncates my end result which is more than 'max' length, how can i prevent that?
-
Richard Deeming almost 4 years@FerdinandGaspar
varchar(max)
can store up to 2GB. If you need to concatenate more than that, you'll need to do it outside of SQL Server. You won't be able to do it in .NET either, since .NET strings also have a maximum length of 2GB. -
neeraja almost 4 yearsThanks for the wonderful explanation for the STUFF function, I couldn't find clear and easy documentation like this anywhere else.
-
user1169587 almost 3 yearswhat is the t2 after temp1 in step3?
-
Martha almost 2 years@user1169587, it's a table alias. It's what the
t2
int1.id = t2.id
refers to.