For XML PATH without element wrapper

I am designing a SSRS report.  In the query's Select statement, one field contains the below Substring function attempting to make a comma separated list as one of the fields.  When the query is run, this field always contains the below wrapper:

 "Expr1>Invalid Address, </Expr1>".

1)  How do I get just the text without the wrapper "Expr1> and "</Expr1>"?
2)  How can it be set up to not display the comma if there is only one, or no items, in the list?


SUBSTRING ((SELECT DISTINCT CSL.MEDIUM_DESC + ', ' AS Expr1
                      FROM STOPLIST AS SL
                             INNER JOIN CSL ON CSL.CODE_VALUE_KEY = SL.STOP_REASON
                              WHERE     (SL.PEOPLE_ID = ACA.PEOPLE_ID) FOR XML PATH('')), 2, 200) AS REASON_LIST
nuladAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
Try:
 
SUBSTRING ((SELECT DISTINCT ', ' + CSL.MEDIUM_DESC
                      FROM STOPLIST AS SL 
                             INNER JOIN CSL ON CSL.CODE_VALUE_KEY = SL.STOP_REASON
                              WHERE     (SL.PEOPLE_ID = ACA.PEOPLE_ID) FOR XML PATH('')), 3, 200) AS REASON_LIST

Open in new window

0
 
nuladAuthor Commented:
Thank you so much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.