FOR XML Path

Question

FOR XML Path

ringovski

Hall of Fame

Points: 3521
More actions
March 23, 2016 at 7:11 pm

#309344

Hi,
I have inherited a report from a old colleague and you can see that he has used the FOR XML Path command for one of the columns previous approvers.
The responsible column has user id's in it and the script is resolving the id to the name. but somehow it displaying the user names when there is more than one name in the column. I know what it does but I don't understand how it works, can anyone explain it.
e.g.
l.responsible column, [previous Approvers]
null, null
123, John Smith
123,456, John Smith, Roger Hammersmith
,SUBSTRING((SELECT ', ' + gen_Firstname + ' ' + gen_Lastname
FROM dbo.users
WHERE [USER_ID] IN (SELECT items FROM [dbo].[SplitCSV] (l.Responsible,','))
ORDER BY gen_Firstname FOR XML Path('')),2,8000) as [Previous Approvers]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

ringovski (3/23/2016)
Hi,
I have inherited a report from a old colleague and you can see that he has used the FOR XML Path command for one of the columns previous approvers.
The responsible column has user id's in it and the script is resolving the id to the name. but somehow it displaying the user names when there is more than one name in the column. I know what it does but I don't understand how it works, can anyone explain it.
e.g.
l.responsible column, [previous Approvers]
null, null
123, John Smith
123,456, John Smith, Roger Hammersmith
,SUBSTRING((SELECT ', ' + gen_Firstname + ' ' + gen_Lastname
FROM dbo.users
WHERE [USER_ID] IN (SELECT items FROM [dbo].[SplitCSV] (l.Responsible,','))
ORDER BY gen_Firstname FOR XML Path('')),2,8000) as [Previous Approvers]

Basically this code is delivering you an XML document with nodes containing the strings you want (each prepended with a comma) where the nodes have no beginning or ending XML tags around them ( per the empty path in PATH('') ).

I actually wrote a SQLCLR to do this kind of group-concatenation in a cleaner fashion (no XML tricks) that outperforms the XML methods and allows for a much cleaner query form. If you want to read more about the FOR XML, the SQLCR and other methods too see this article from Aaron Bertrand. The performance benchmarks are at the bottom of the article:

http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

If you want to try it out you can get my SQLCLR Aggregate Function at: http://groupconcat.codeplex.com

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

Let's reformat the code to make it more readable:

SUBSTRING

(

SELECT ', ' + gen_Firstname + ' ' + gen_Lastname

FROM dbo.users

WHERE [USER_ID] IN (SELECT items FROM [dbo].[SplitCSV] (l.Responsible,','))

ORDER BY gen_Firstname FOR XML Path('')

),2,8000

) as [Previous Approvers]

This:

DECLARE @string varchar(100) = 'abcdef';

SELECT SUBSTRING(@string,2,8000);

Returns: bcdef

This:

DECLARE @Responsible varchar(100) = 'xxx,yyy,zzz';

SELECT * FROM dbo.SplitCSV(@Responsible,',');

Returns:

Items

--------

xxx

yyy

zzz

This:

SELECT ', ' + gen_Firstname + ' ' + gen_Lastname

FROM dbo.users

WHERE [USER_ID] IN (SELECT items FROM [dbo].[SplitCSV] (l.Responsible,','))

Concatinates a comma, gen_Firstname, and gen_Lastname from dbo.users which is filtered by joining the results of [dbo].[SplitCSV] to dbo.users on USER_ID = items

And, as Orlando said (he appears to have beat me to it) the XML PATH('') portion of the subquery turns the results of the subquery into a single string. The SUBSTRING function begins at the 2nd position to skip the first comma.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

ringovski Hall of Fame Points: 3521 More actions · Answer 3

Thanks Orlando and Alan for the replies.

Is dbo.splitcsv a reserved table or function somewhere as i am unable to find it within the DB?

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

ringovski (3/23/2016)
Thanks Orlando and Alan for the replies.
Is dbo.splitcsv a reserved table or function somewhere as i am unable to find it within the DB?

It's a custom, user-created function which you can find in your DB. To see how it works you could open SQL Server Management Studio, browse to the DB > Prgramability > Functions > Table-valued functions. You will see it there. From there you can right-click it, Select "Script Function as" > Create to > New Query window. That will show you the code used to create the function.

On that note, the splitter you really want is DelimitedSplit8K[/url]. Its the fastest splitter out there.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001