As I dig more into Informatica and MySQL, found out that we were not properly copying XML column from SQL Server into MySQL.
Here is our table structure.
Original Table Structure
Physical Data Object – Admin.CourseManagerUserLog
Later on, was cognizant that our XML Column, details, precision or size is 0
Later on, noticed that for the details column, the arrow is not full.
Run the mapping.
And, things ran well. Was not until later that I found that the details column on the destination, MySQL, was empty.
Create View on the Source
Here is a workaround.
In the view, add new columns to track the unsupported XML Column.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO if object_id('[Admin].[CourseManagerUserLog_XMLColumnConverted]') is null begin exec('create view [Admin].[CourseManagerUserLog_XMLColumnConverted] as select 1/0 as [shell] ') end go alter view [Admin].[CourseManagerUserLog_XMLColumnConverted] as SELECT [id] , [username] , [resourceID] , [status] , [message] , [details] , [detailsAsVarcharMax] = cast( [details] as varchar(max)) , [detailsAsVarchar] = cast( [details] as varchar(8000)) , [userID] , [refID] , [record_created] , [appointmentID] from [Admin].[CourseManagerUserLog] GO GRANT SELECT ON [Admin].[CourseManagerUserLog_XMLColumnConverted] TO [public] go
- details, XML Column, comes in xml. But, size is 0
- detailsAsVarchar, varchar column, size is 8000 ( max size in MS SQL Server )
- detailsAsVarcharMax [varchar(max) ], size is 0, as well
After AutoLink ….
Those columns with matching names are Auto Linked.
After Column – detailsAsVarchar is linked to details
And, then we manually linked the columns whose names are not matched.