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
Release : 4.9.1
Component : CA Test Data Manager - Subset
The data type conversion is being handled in SubSet-4.9.50.46 patch.