For XML Path with special characters

1

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.

2

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
3

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

4

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
5

thanks, my issue has been fixed.

6

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