How do you migrate XML stored in a TEXT field to an XML Data type?
I have XML stored in a TEXT field in a SQL Server 2000 database and I would like to migrate that data to a SQL Server 2005 XML data type field. I setup an OLEDB Source and a SQL Server Destination in SSIS in my data flow task and attempted to copy a TEXT field containing XML data to an XML data type in the detination database. When doing so, I received the following error:
Error at Data Flow Task[OLE DB Destination ): Columns "TransactionData" and "TransactionXML" cannot convert between unicode and non-unicode string data types.
Has anyone attempted this yet? I looked at existing T-SQL functions such as CONVERT and CAST and both of them return a similar error message.
I thought about creating a custom CLR function to read the string in and convert it to an XML document but you would think there would be some straightforward way to do this considering to the fact that most people probably stored XML in TEXT blobs prior to SQL 2005.