FOR XML PATH

Question

FOR XML PATH

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
June 13, 2016 at 1:30 am

#400267

Hi there,
I have the following TSQL
select STUFF(( SELECT ISNULL(',' + ltrim(rtrim(l.Description)) + ' ' + ltrim(rtrim(c.Surname)),'')
FROM ContactDetail c
LEFT JOIN ContactGroupMember cgm ON cgm.ContactId = c.ContactId
LEFT JOIN Lookup l on l.LookupReference = c.TitleId and l.LookupTypeId = 48
WHERE cgm.Responsible = 1 AND cgm.ContactGroupId = cg.ContactGroupId
FOR XML PATH ('')
),1,1,'') as 'ResonsibleName'
,CG.ContactGroupId
FROM ContactGroup cg
WHERE cg.ContactGroupId = 68000
This piece of code pulls me back the following result -
Mr A,Ms B
So basically both Mr A and Ms B are part of the same Contact Group and are both marked as "responsible" for that contact group so I pull them pack into 1 field.
The issue I have is that I don't want a comma to separate my results, I would like an &.
When I replace the , in my code with an & (just after the ISNULL) - I get the following result instead -
amp;Mr Nicholls&Ms Wainwright
I don't want that - I require -
Mr Nicholls&Ms Wainwright

Viewing 7 posts - 1 through 6 (of 6 total)

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

Thomas Rushton SSC-Insane Points: 22649 More actions · Answer 1

I'm guessing that that would be because you're going through an XML processor, which translates an & into the XML / HTML equivalent - "&". You will therefore need to change the parameters of the STUFF command to (..., 1, 5, '') instead of (..., 1, 1, '')

Thomas Rushton
blog: https://thelonedba.wordpress.com

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 2

Thanks - nearly there. So it's taken care of the & at the start of the result set...but the delimiter in between is still coming back as &amp.

Mr Nicholls&Ms Wainwright

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

This has done the trick -

select replace (STUFF(( SELECT ISNULL(';' + ltrim(rtrim(l.Description)) + ' ' + ltrim(rtrim(c.Surname)),'')

FROM ContactDetail c

LEFT JOIN ContactGroupMember cgm ON cgm.ContactId = c.ContactId

LEFT JOIN Lookup l on l.LookupReference = c.TitleId and l.LookupTypeId = 48

WHERE cgm.Responsible = 1 AND cgm.ContactGroupId = cg.ContactGroupId

FOR XML PATH ('')

),1,1,'')

,';',' & ')as 'ResonsibleName'

,CG.ContactGroupId

FROM ContactGroup cg

WHERE cg.ContactGroupId = 68000

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

Same thing happens when you encounter a < or > in your text. You could nest your statement in even more replace statements, but your code isn't going to look better from doing that. So instead of doing the translation yourself, why not use SQL server's xml routines to do that translation for you?

The first thing you need to do is let sql server know you want an XML type returned by the for xml path() clause. You do that by adding ", type" after the for xml path()-clause.

Next, now that the subquery returns an xml value, you can query this xml value for it's value by using the .value() function. If you ask that to return the string value for the root node of the xml, it will return you all string values concatenated. You've already got the code in place to strip off the leading ',', so we insert the .value() function inside that stuff() function. The resulting code is as follows:

select STUFF(( SELECT ISNULL(',' + ltrim(rtrim(l.Description)) + ' ' + ltrim(rtrim(c.Surname)),'')

FROM ContactDetail c

LEFT JOIN ContactGroupMember cgm ON cgm.ContactId = c.ContactId

LEFT JOIN Lookup l on l.LookupReference = c.TitleId and l.LookupTypeId = 48

WHERE cgm.Responsible = 1 AND cgm.ContactGroupId = cg.ContactGroupId

FOR XML PATH (''), type

).value('.','nvarchar(max)'),1,1,'') as 'ResonsibleName'

,CG.ContactGroupId

FROM ContactGroup cg

WHERE cg.ContactGroupId = 68000

I additionally suggest adding an order by clause to the sub query, for better readability and reproducable results.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

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

Just to add to what R.P. Rozema said...

Here is a good article about this technique for concatenating a string using this method: Creating a comma-separated list (SQL Spackle)[/url].

"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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

TSQL Tryer (6/13/2016)
Hi there,
I have the following TSQL
select STUFF(( SELECT ISNULL(',' + ltrim(rtrim(l.Description)) + ' ' + ltrim(rtrim(c.Surname)),'')
FROM ContactDetail c
LEFT JOIN ContactGroupMember cgm ON cgm.ContactId = c.ContactId
LEFT JOIN Lookup l on l.LookupReference = c.TitleId and l.LookupTypeId = 48
WHERE cgm.Responsible = 1 AND cgm.ContactGroupId = cg.ContactGroupId
FOR XML PATH ('')
),1,1,'') as 'ResonsibleName'
,CG.ContactGroupId
FROM ContactGroup cg
WHERE cg.ContactGroupId = 68000
This piece of code pulls me back the following result -
Mr A,Ms B
So basically both Mr A and Ms B are part of the same Contact Group and are both marked as "responsible" for that contact group so I pull them pack into 1 field.
The issue I have is that I don't want a comma to separate my results, I would like an &.
When I replace the , in my code with an & (just after the ISNULL) - I get the following result instead -
amp;Mr Nicholls&Ms Wainwright
I don't want that - I require -
Mr Nicholls&Ms Wainwright

Just my take on how I would do this. Not sure if this will work as I have nothing to test against.

select

STUFF(( SELECT

ISNULL(',' + ltrim(rtrim(l.[Description])) + ' ' + ltrim(rtrim(c.Surname)),'')

FROM

ContactDetail c

LEFT JOIN ContactGroupMember cgm

ON cgm.ContactId = c.ContactId

LEFT JOIN [Lookup] l

on l.LookupReference = c.TitleId and l.LookupTypeId = 48

WHERE

cgm.Responsible = 1 AND

cgm.ContactGroupId = cg.ContactGroupId

FOR XML PATH (''),TYPE).value('.','varchar(max)'),1,1,'') as 'ResonsibleName'

,CG.ContactGroupId

FROM

ContactGroup cg

WHERE

cg.ContactGroupId = 68000;