How To Migrate MS SQL XML field to MySQL

Question

I have a field type of XML in MSSQL and I would like to migrate them to MySQL 5, currently all other fields are running fine with MySQL Migration Toolkit, but this XML field is not handled.

Any suggestions? Thanks

Answer 1

There are some interesting pages regarding the benefits of LONGTEXT vs VARCHAR. e.g.

Long text or Varchar

In newer MYSQL builds (after 5.0.3) VARCHAR is much preferred for field sizes you can safely predict will be less than 64k. While many XML documents may be longer than this, its worth consideration. 5.0.3 and prior builds restrict VARCHAR to 0-255 and so a TEXT type is almost certainly preferable.

A good discussion of the BLOB types (including TEXT types) can be found here:

MYSQL 5.1 reference manual, 10.4.3 The Blob and Text Types

I imagine a MEDIUMTEXT type (up to 16Mb) will suffice for all but the most verbose XML documents) - saving you a solitary byte per record over LONGTEXT.

Answer 2

If you don't need the extra features that the XML type gives you (checking for well-formed XML, schema support etc), the just store the XML text in a LONGTEXT