Synapse:CopyActivity,Upsert option:The data type xml cannot be used as operand to the UNION, INTERSECT or EXCEPT operators because it not comparable

Sowmya V 0
Viewed 50

Synapse:CopyActivity,Upsert option:The data type xml cannot be used as operand to the UNION, INTERSECT or EXCEPT operators because it not comparable In database i checked sink table have 2 xml column so i am getting error. How to over come this error. I dont want to change datatype in table.

azure-synapse
1 Answers

I used script activity in synapse to cast xml data to varchar in each table and then to union all the tables. Then I copied the data to target table which has xml data type using copy activity. Below are the steps.,

  • Source table and target table has xml data type column Tables with xml data type before union

  • Use the below script in script activity for dynamically converting all xml data to varchar.

Declare @column_list varchar(max)
Set @column_list = (
SELECT  
  STUFF((SELECT  ',' + case when system_type_id= 241  then 'Cast(' +  name + ' as nvarchar(max)) as ' + name Else name End FROM sys.columns SCI
            WHERE   SCI.object_id =SCO.object_id 
            ORDER BY column_id 
        FOR XML PATH('')), 1, 1, '') AS 'a'
FROM sys.columns SCO where object_id = object_id('table1') 
group by object_id) 
  
Declare @sql nvarchar(max) = ''
Set @sql = 'Drop table if exists union_stg_table; Select  * into union_stg_table From (
Select ' + @column_list + ' From table1
union
Select ' + @column_list + ' From table2
union 
Select ' + @column_list + ' From table3
)A'
exec sp_executesql @sql

Script activity

  • Copy the script activity's output table into the target table using copy activity. Target table has xml data type.

result table

As wbob-MSFT has suggested, you can also use stored procedure for transformation. Similarly, we can use intersection, except statements between two tables with the above approach.

Aswin0
Related