VCHAR To XML After Table Is Load Via SSIS

I loaded a vchar column using script trans (SSIS doesn't support XML data types :/) to format the XML data properly. Since the XML data in the vchar column was not encoded, when I try to alter the vchar column to xml after the table is loaded it fails. I assume this is because the parser is having trouble with the content of the data within the xml tags.

This is how I coded the XML rather than using a xml method. "true" places the element end tag.




newXML += FormatElement("CONFERENCE") _

+ FormatElement("NAME") + ConvertToESC(RTrim(Mid(Row.activity, 1, 34))) _

+ FormatElement("NAME", True) _

+ FormatElement("START_DATE") + ConvertToESC(RTrim(Mid(Row.activity, 35, 8))) _

+ FormatElement("START_DATE", True) _

+ FormatElement("END_DATE") + ConvertToESC(RTrim(Mid(Row.activity, 43, 8))) _

+ FormatElement("END_DATE", True) _

+ FormatElement("CONFERENCE", True)


My question is, can I either load the data, using T-SQL, into an XML data type from a vchar column or is there a way to alter the type from vchar to xml without encountering the following parsing error?



Msg 9421, Level 16, State 1, Line 1

XML parsing: line 1, character 64, illegal name character

Thanks for any suggestions!

James