Hi again

The powers that be have now decided they only need the first 300 characters from the XML-based code I'm currently using.

I've left the code as-is and I'm using

left([Additional Notes].Comments,300)

to get the bit I need.

Is there any way of restricting the code below to just return 300 characters (hopefully it will speed things up as well):-

select

mult1.VisitID

,mult1.AbstractID

,replace(replace(replace(replace((

select

Response + ','

from

[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult2

where

mult2.VisitID=mult1.VisitID

and mult2.AbstractID=mult1.AbstractID

and mult2.[Query]='Additional Notes'

order by

mult2.VisitID

,mult2.AbstractID

for xml path ('')),' ',''),'&amp','&'),char(13),''),char(10),'') as Comments

from

[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult1

where

mult1.[Query]='Additional Notes'

group by

mult1.VisitID

,mult1.AbstractID) [Additional Notes]

It doesn't take too long (about 20 minutes) to sort out just over 1,000,000 records (there are quite a few joins) but a saving is a saving.

I've already enquired about adding indexes (there aren't any :angry:) and that should speed things up as well.

I can't add the indexes directly as this is a third-part app that writes to a SQL database.

Any pointers gratefully received).

As an aside, I don't quite understand how the FOR XML PATH thing works and the link didn't really explain it (nor does any site I've found courtesy of Google). Would someone kindly explain?