I am trying to write a process to parse out multiple values in a field to build another table and get this into some semblance of normal form. The problem I am having is that it appears this particular column contains free form data and there is all sorts of stuff entered. As I try to convert to xml to aid with my process, I get an error saying "Illegal qualified name character', but I cannot for the life if me find it in the data. I am taking into account the normal stuff, gt, lt, amp and so on, and also checked for ellipsis and em dashes and a ton others, but I still can't figure out what is causing this.
I did some research and saw to try CMDATA, but it doesn't seem to be helping either
CAST('<![cdata[' = my column data here + ']]>' as xml)
My questions are, am I using cdata wrong, is there a way to find these seemingly invisible characters, or should I just work on another way? The idea was to parse out the column by char(13) + char(10) into multiple records so each "line" within the notes entered can have their own record that can tie back to the main record.