Improve company productivity with a Business Account.Sign Up

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

Concatenate multiple rows using XML PATH leaves special characters

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
Lensolo
Asked:
Lensolo
  • 5
  • 3
1 Solution
 
Surendra NathTechnology LeadCommented:
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
 
LensoloAuthor Commented:
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
 
LensoloAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Surendra NathTechnology LeadCommented:
0
 
LensoloAuthor Commented:
yeah. That's what I thought they should be, but if you look above, that's not the characters sql returns.
0
 
LensoloAuthor Commented:
Wow. Excellent. Thank you very much!
0
 
LensoloAuthor Commented:
Very thorough, helpful & quick to respond. Thank you very much.
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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