Some uses of Stuff and XML

Question

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

Answer 1

0

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 &, >, <

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
  stuff(
     (select ', <' + name + '>'
     from sys.databases 
     where database_id > 4
     order by name
     for xml path(''), type
     ).value('.','varchar(max)')
   , 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

answered Apr 15 '10 at 12:15 AM

Rob Farley
5.7k ● 13 ● 17 ● 19

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 '10 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 '10 at 05:12 AM Rob Farley

Thanks a lot. Great stuff.

Apr 15 '10 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 '10 at 09:57 PM Rob Farley

(comments are locked)

Answer 2

0

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/

answered Apr 14 '10 at 09:14 PM

TimothyAWiseman
14.3k ● 17 ● 20 ● 29

Thanks for pointing out Jeff's article.

Apr 15 '10 at 02:02 AM CirqueDeSQLeil

(comments are locked)

Some uses of Stuff and XML

Follow this question

Related Questions