Incorrect XML parsing in SQL Text

Question

I have a piece of sql text which uses:

Cast ('< M>' + Replace(JobNote, ',', '< /M>< M>') + '< /M>' AS XML)

and when i execute it the error generated is:

XML parsing: line 1, character 23, illegal name character can someone tell me what should I do??

(please ignore the blankspace before M> in < M>)

Varchar ....JobNote contains string i am actually trying http://stackoverflow.com/questions/27543755/appending-data-in-t-sql/27551232#27551232 so i have another part in the first select which fetches: Split.a.value('.', 'VARCHAR(100)') JobNote — Somdip Dey, Dec 18 '14 at 16:45
Alex K mean what is the content of JobNote. Some character in there is illegal — Sparky, Dec 18 '14 at 16:47
oh. such as &, , etc..you mean?? what if I do need to keep those characters.....is there some way around this? — Somdip Dey, Dec 18 '14 at 16:49
Don't cast as XML? You can't use that output externally, so... — Aaron Bertrand, Dec 18 '14 at 16:57
i am new to this sql and xml casting...can someone give an example of how to go around it?? — Somdip Dey, Dec 18 '14 at 17:01

score 1 · Accepted Answer · answered Dec 18 '14 at 17:34

The space in < M> is what not allowing to cast the string as XML remove the space like

select Cast ('<M>' + Replace(JobNote, ',', '</M>') + '</M>' AS XML)

then it will work fine.

And There are few special characters are invalid in XML so it has to be replaced in JobNote with the following.

& - &amp;
< - &lt;
> - &gt;
" - &quot;
' - &#39;

select Cast ('<M>' + Replace(JobNote, ',', '</M>< M>') + '</M>' AS XML)

1 Answers1