Issue in SSRS while using XML PATH in SQL query
Hi All, I have used a SQL query which concatenates multiple rows into one single row seperated by a coma. select NoteID, STUFF( (SELECT ',' + a.Comments AS [text()] from Notes a where a.NoteID = b.NoteID Order by a.Comments for xml PATH('')),1,1,'' ) AS Comments_Concatenated from Notes b group by NoteID ORDER BY NoteID Here I have a Note ID and Comment Column Note ID Comment 100 Text 1 100 Text 2 101 Text 3 101 Text 4 By using the above query , when i run it in SQL management studio I get NoteID Comment 100 Text1,Text2 101 Text3,Text4 However when i use the same query in SSRS query designer I get the reuslt as Note ID Comment 100 <Expr>Text1 </Expr>,<Expr>Text2</Expr> 101 <Expr>Text3 </Expr>,<Expr>Text4</Expr> The additional strings <Expr> are getting added. I think the XML Path in the query is creating the problem. How do i overcome in SSRS. I want to remove the additional strings <Expr>. Please let me know how to achieve this in SSRS. Thanks, Ram
April 13th, 2011 1:21pm

Hi Ram, Based on the dataset and query you posted, I did a test but it works fine in both SQL Server Management Studio (SSMS) and Query Designer in Business Intelligence Development Studio (BIDS). In the Query Designer of BIDS, I got the following result without the additional strings <Expr> and </Expr> NoteID Comments_Concatenated 100 Text 1,Text 2 101 Text 3,Text 4 Since it generates 2 data fields NoteID and Comments_Concatenated, could you please specify them to the Detail Data row of a table to see if the works fine when the report is rendering? If it the additional string still exists, please post and screenshot and the version of your Reporting Services. Thanks, Tony Chain Tony Chain [MSFT] MSDN Community Support | Feedback to us Get or Request Code Sample from Microsoft Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Free Windows Admin Tool Kit Click here and download it now
April 17th, 2011 10:39pm

Hi Tony Chain, Aplogize for the delayed reply. I had to change the SQL query for solving the above problem. Thanks , Ram
April 23rd, 2011 7:28am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics