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 stackoverflow.com/questions/27543755/appending-data-in-t-sql/… so i have another part in the first select which fetches: Split.a.value('.', 'VARCHAR(100)') JobNote — Somdip Dey
– Somdip Dey, Commented Dec 18, 2014 at 16:45
Alex K mean what is the content of JobNote. Some character in there is illegal — Sparky
– Sparky, Commented Dec 18, 2014 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
– Somdip Dey, Commented Dec 18, 2014 at 16:49
Don't cast as XML? You can't use that output externally, so... — Aaron Bertrand
– Aaron Bertrand, Commented Dec 18, 2014 at 16:57

Pரதீப் · Accepted Answer · 2014-12-18 17:34:12Z

1

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)

answered Dec 18, 2014 at 17:34

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1