question

madhav3115 avatar image
madhav3115 asked

Handling Special Characters while using FOR XML, Path

We are using For XML with Stuff to read XML values, however in the output the code returns very differently i.e the Carraige return returned for a special character is not consistent which is creating discrepancies while using REPLACE Function

<(>&<)> For &

<(><<)> for <

<(> <<)> For <

& For &

> for >

for &

Even after replacing the above Carraige returns with the Replace function, we still see discrepancies , like at certain place Full stop (.) is returned as (.&), (:) is returned as (:&) etc. Such occurrence is not consistent throughout which made us realize that REPALCE Function is not a VIABLE Alternative.

and we might have to do something with FOR XML,Path Syntax only. Below is the Representation of the code:

SELECT ID

,ID1

,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(STUFF(

(SELECT' '+Text

FROM

[#Temp1] T

WHERE T.[ID] = T1.[ID]

AND T.[ID1] = T1.[ID1]

FORXMLPATH('')

),1,1,''),'<(>&<)>','&'),'<(><<)>','<'),'<(> <<)>','<'),'&','&'),'>','>'),' ','&')AS [ID3]

,TSTP

FROM

[#Temp1] AS T1

sql server 2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered

To fix a lot of XML string mistranslation problems like these, use

SELECT STUFF((SELECT … FOR XML PATH (''), type).value('.','varchar(max)'),1,1,'')
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.