INSERT scripts do not incorporate the CONVERT (XML,...) function to allow for INSERT of the XMLData datatypes

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

While doing some internal testing of GT Subset, we ran across an issue associated with Subset (4.9.100.37) / Datamaker (4.9.1015) and SQL Server 2019 with XMLData datatypes. The resulting INSERT scripts do not incorporate the CONVERT (XML,...) function to allow for INSERT of the XMLData from one db to another.

For example, when using standard sample db AdventureWorks 2019:

Subset query:

SELECT *
FROM Person.Person
where Demographics.exist ('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; (/ns:IndividualSurvey/ns:Education[contains (.,"Bachelors")])')=1
AND Demographics.exist ('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" ; (/ns:IndividualSurvey/ns:Gender[contains (.,"F")])')=1

Example insert output:

INSERT INTO [AWtarget].[Person].[Person] ([BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] )
SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2019].[Person].[Person] L0 WHERE ( Demographics.exist ( 'declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; ( /ns:IndividualSurvey/ns:Education[contains ( . , "Bachelors" ) ] ) ' ) =1 AND Demographics.exist ( 'declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; ( /ns:IndividualSurvey/ns:Gender[contains ( . , "F" ) ] ) ' ) =1 )
GO

Error message when executing INSERT scripts:

STARTING INSERTS FOR [AWtarget].[Person].[Person] AT 30 Apr 2021 19:24:06:417
Msg 527, Level 16, State 2, Server <HostName>, Line 1
Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query.

Successful insert output:

INSERT INTO [AWtarget].[Person].[Person] ([BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,[AdditionalContactInfo] ,[Demographics] ,[rowguid] ,[ModifiedDate] )
SELECT [BusinessEntityID] ,[PersonType] ,[NameStyle] ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] ,[Suffix] ,[EmailPromotion] ,CONVERT(XML,[AdditionalContactInfo]) ,CONVERT(XML,[Demographics]) ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2019].[Person].[Person] L0 WHERE ( Demographics.exist ( 'declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" ( /ns:IndividualSurvey/ns:Education[contains ( . , "Bachelors" ) ] ) ' ) =1 AND Demographics.exist ( 'declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey" ( /ns:IndividualSurvey/ns:Gender[contains ( . , "F" ) ] ) ' ) =1 )
GO

Cause

According to Microsoft, "Implicit conversions between XML data types are supported only if the source or target is untyped XML. Otherwise, the conversion must be explicit."