Solved

Concatenate multiple rows using XML PATH leaves special characters

Posted on 2014-02-20
8
1,734 Views
Last Modified: 2014-02-20
I am trying to find a way to concatenate comments from multiple rows in a view. The best way I can get it to work is using XML PATH, but it converts special characters to "&#gt;" or "
", & ";&".

Is there a way to either clean up the special characters (not using replace - I want to know that I got them all) or is there another way to do this in a view?

SELECT  B.ID,
          Stuff((SELECT Comments
                     FROM TableName A
                     WHERE B.ID = A.ID
                     FOR XML PATH ('')),1,0,'')
FROM   TableName B
GROUP BY ID
0
Comment
Question by:Lensolo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39875179
No, there is no way to clean the special characters without using the replace function.
is there an other way to do this on a view, yes definetly but it may not be a as performant as the XML path thing.

the old school method:

declare @T CURSOR FOR
SELECT Comments
                     FROM TableName A,TableName B
                     WHERE B.ID = A.ID

OPEN @T
FETCH @T INTO @Comments

WHILE (@@FETCH_STATUS  = 0)
BEGIN

SELECT @CONCAT = @CONCAT + @comments
FETCH @T INTO @Comments

END

Close @T
DEALLOCATE @T


now the above method certainly works, but it will hit a performance bottleneck when there is a huge load..

so we suggest to go with the replace + xm path instead.
0
 

Author Comment

by:Lensolo
ID: 39875199
Thanks. Is there a list of all possible values I have to clean up (or if you can tell me what these are called I can google it)? If that's the cleanest option, I'll do it. I just need to make sure I have a full list.
0
 

Author Comment

by:Lensolo
ID: 39875202
Also, I found commenst that TYPE can be added to the end of atleast XML AUTO to fix this, but I can't seem to find a way to get this added to the end of XML PATH
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39875221
0
 

Author Comment

by:Lensolo
ID: 39875226
yeah. That's what I thought they should be, but if you look above, that's not the characters sql returns.
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 500 total points
ID: 39875228
0
 

Author Comment

by:Lensolo
ID: 39875232
Wow. Excellent. Thank you very much!
0
 

Author Closing Comment

by:Lensolo
ID: 39875237
Very thorough, helpful & quick to respond. Thank you very much.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 65
EF5 How do I stop pre-compiled views? 8 54
SQL State HYT00. Timeout expired proplem 8 44
Convert DateTime format in SQL query 7 18
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question