nulad
asked on
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
"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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER