vendredi 10 avril 2015

Convert a VARCHAR into XML that contains reserved characters

Basically I am trying to split a string based on delimiter. Below is partial code -



DECLARE @Str VARCHAR(MAX)
DECLARE @delimiter CHAR(3)
DECLARE @xmlPD xml

SET @delimiter = '~~~'
SET @Str = 'abc~~~I&IK blah blah'
SET @xmlPD = CAST(('<X>'+REPLACE(@Str, @delimiter, '</X><X>')+'</X>') AS XML)


The problem is as @Str contains & very last line is throwing below error.



Msg 9411, Level 16, State 1, Line 8
XML parsing: line 1, character 18, semicolon expected


How can I handle this scenario.


NOTE:




  • I will insert the splitted data into a table. So, I have to insert & as & not &amp;.




  • @Str may contain & as well as &amp;




  • We are using SQL-SERVER 2005




Aucun commentaire:

Enregistrer un commentaire