question

CirqueDeSQLeil avatar image
CirqueDeSQLeil asked

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
t-sqlscriptfor-fun
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered

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/

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for pointing out Jeff's article.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.