[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

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
0
nulad
Asked:
nulad
1 Solution
 
Nico BontenbalCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now