SQLTeam.com | Weblogs | Forums

For XML Path with special characters

Hi,

I need help in parsing the XML path when there is special character in the string like '&', it is splitting the string into new value.

For example
AccountNo -------CodeDesc
1234---------------Testing Tubes
1234--------------HighTech Lamps
1234-------------Handling Charge & Surcharge
1234-------------transfer Fee

When I do below

SELECT DISTINCT ';' + CodeDesc FROM Table1
for XML Path(' ')

instead of 4 values it giving 5 values

; Testing Tubes ; HighTech Lamps ;Handling Charge & amp ; Surcharge ; transfer Fee

Not sure why it is separating the text into two values, please provide any suggestion to prevent this

thanks in advance.

I got below code Online and it fixed my issue, hopefully it is useful for someone.

// GOOD approach (either use case 1 or below option)

select stuff(

(select ', ' + countryname

from Country

for xml path(''), root('MyString'), type

).value('/MyString[1]','varchar(max)')

, 1, 2, '') as delimitedCountryName;

1 Like

If you're using SQL Server 2017 or above, you can use the STRING_AGG() function for this now.

It's not actually returning 5 values, it's returning 4. The ampersand is being converted to amp; for XML. It looks like 5 because you are using ; for a delimiter. If you change to a pipe, you'll see it is only 4 values. If 2017 or above, I'd follow Jeff's advice

1 Like

thanks, my issue has been fixed.

It would be real friendly of you to explain how your issue has been fixed so that others can figure it out when they read the multiple suggestions on this post.

1 Like