Some uses of Stuff and XML

There are probably a ton of uses for combining Stuff() with For XML Path. My question is what are some of the varied uses you have encountered.

I'll start off with a snippet of a larger script. This snippet concatenates the index columns into a single field.

SELECT  icol.object_id  ,i.name  ,icol.index_id  ,STUFF(  (  SELECT ',' + c.name AS [text()]  FROM sys.index_columns ic  Inner Join sys.columns c  ON ic.object_id = c.object_id  AND ic.column_id = c.column_id  WHERE ic.object_id = icol.object_id  AND ic.index_id = icol.index_id  ORDER BY ic.key_ordinal  FOR XML PATH('')  ), 1, 1, '') AS ColList  FROM sys.index_columns icol  Inner Join sys.indexes i  ON icol.object_id = i.object_id  AND icol.index_id = i.index_id GROUP BY icol.object_id, i.name, icol.index_id 
more ▼

asked Apr 14, 2010 at 08:54 PM in Default

CirqueDeSQLeil gravatar image

4.9k 10 11 15

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

Reporting is the obvious answer. I also use it for providing a dynamic pivot. Actually, I use it remarkably regularly, and don't really think about the specific cases.

But actually, you should change the way you use it. You'll notice that if you have an ampersand (or > or <) in your string, that this gets "HTMLEncoded" into &amp;, &gt;, &lt;

The way to get around this is to do it slightly differently. Like this:

(select ... for xml path(''), type).value('.','varchar(max)')

Like this:

     (select ', <' + name + '>'
     from sys.databases 
     where database_id > 4
     order by name
     for xml path(''), type
   , 1, 2, '') as dblist;

....which I've blogged about at http://sqlblog.com/blogs/rob_farley/archive/2010/04/15/handling-special-characters-with-for-xml-path.aspx

more ▼

answered Apr 15, 2010 at 12:15 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

I like it. I would prefer to use the method that you just demonstrated rather than the text. I have noticed that on other scripts and have found other viable solutions. I figured for this script it wasn't necessary since the concatenation is on column names.
Apr 15, 2010 at 02:01 AM CirqueDeSQLeil
Using text() is just the same as using [*] or having no column name (as I've done). But even if you do, you still lose the special characters. Using root, type and .value will handle that for you though.
Apr 15, 2010 at 05:12 AM Rob Farley
Thanks a lot. Great stuff.
Apr 15, 2010 at 03:31 PM CirqueDeSQLeil
Ah - I should change it... you don't actually need the ROOT, because you can use '.' as the first argument in value...
Apr 15, 2010 at 09:57 PM Rob Farley
(comments are locked)
10|1200 characters needed characters left

I think the vast majority of the use cases will be for string contatentation very similar to what you have done. I have frequently used it to turn attributes stored in a many-to-one relationship with the entity into a single string for display or use in reports. I frequently turn those things into views to make them easier to query while still maintaining the base data in a normalized form.

I won't provide a code snippet, because Jeff Moden has already done it better than I could. His article at
Performance Tuning: Concatenation Functions and Some Tuning Myths http://www.sqlservercentral.com/articles/Test+Data/61572/

more ▼

answered Apr 14, 2010 at 09:14 PM

TimothyAWiseman gravatar image

15.6k 21 23 32

Thanks for pointing out Jeff's article.
Apr 15, 2010 at 02:02 AM CirqueDeSQLeil
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 14, 2010 at 08:54 PM

Seen: 5525 times

Last Updated: Apr 14, 2010 at 08:54 PM