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."

https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15

Environment

Release : 4.9.1

Component : CA Test Data Manager - Subset

Resolution

The data type conversion is being handled in SubSet-4.9.50.46 patch.

Additional Information

I researched the SQL error and found this Microsoft SQL Server 2019 document discussing Data Type Conversions. See https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15