topic Re: Can't move xml to Snowflake AWS target in Qlik Replicate

Can't move xml to Snowflake AWS target

xiniavargas — Fri, 16 Feb 2024 21:34:31 GMT

We are moving data from SQL to Snowflake AWS.

We have a table that contains a varbinary max which has an xml. To see the XML in SQL, we use this query: SELECT CONVERT(xml,(CONVERT(varbinary(max),columnname)))

We first left everything as default in Qlik, when we moved the data, Snowflake type was binary and when we ran this "SELECT HEX_DECODE_STRING(TO_VARCHAR("columnname"))" the XML was missing information as binary type has a limit in Snowflake.

So, we tried to change the Transforms options in Qlik, we have tried the following:

#1 go to table settings, select column and change the type to CLOB and the subtype to XML, when we run the load, we see Snowflake type as variant, but table is not loaded and we see this error

"SQL_ERROR SqlState: 22P02 NativeError: 100100 Message: Error parsing XML: 0x3C526571756573743E3C46415353657276696365436F6E74726F6C3E3C4D6573736167654C616E6775616765436F64653E6"

#2 go to table settings, select column and change the type to CLOB and the subtype to XML and add an expression "hex(columnname)", we get error

FROM (select $1, $2, $3, parse_xml($4), $5, $6, $7, $8 FROM '@"DB"."PUBLIC"."ATTREP_IS_DB_23b83639_7a18_cb46_add0_b51bbec25be6"/80/') files = ('LOAD00000001.csv.gz') force=true
RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column

there are no nulls in the table, we are not sure what we are missing. Any help would be appreciated.

Re: Can't move xml to Snowflake AWS target

john_wang — Sat, 17 Feb 2024 05:41:47 GMT

Hello @xiniavargas ,

Thanks for reaching out to Qlik Community!

We first left everything as default in Qlik, when we moved the data, Snowflake type was binary and when we ran this "SELECT HEX_DECODE_STRING(TO_VARCHAR("columnname"))" the XML was missing information as binary type has a limit in Snowflake.

I'm afraid I did not get the meaning of "XML was missing information as binary type", all work fine for me. As a sanity test, the SQL Server data type varbinary(max) maps to BINARY(8388608) in Snowflake, the query you mentioned works for me as well:

or in DBeaver:

While migrating the data from Microsoft SQL Server to Snowflake, the data type mapping should be considered. For example in Microsoft SQL Server the data type varbinary(max) can be up to 2G size, but in Snowflake the data type BINARY maximum length is 8 MB. So in task setting, the Limit LOB size to (KB) maximum valid value is 4096 (KB), Qlik Replicate User Guide does not mention this limitation however it's a common rule of heterogeneous databases replication.

Hope this helps.

John.

Re: Can't move xml to Snowflake AWS target

xiniavargas — Mon, 19 Feb 2024 14:22:51 GMT

Our XMLs are too big to be a binary in Snowflake, when Qlik migrates them to binary, not all the XML data is in the column and it is missing information, that is why we need to migrate them to variant, but we haven't been able to move the contents to Snowflake per the errors mentioned in the main thread.

Re: Can't move xml to Snowflake AWS target

john_wang — Mon, 19 Feb 2024 14:53:06 GMT

Hello @xiniavargas ,

Are you meaning the XMLs size in source side exceed 8M so it's truncated in Snowflake? what's the max and average size of the XMLs.

thanks,

John.

Re: Can't move xml to Snowflake AWS target

xiniavargas — Mon, 19 Feb 2024 15:13:14 GMT

Yes. Average 3MB, Max 12MB

Re: Can't move xml to Snowflake AWS target

john_wang — Tue, 20 Feb 2024 12:31:15 GMT

Hello @xiniavargas ,

Thanks for the update , seems the behavior can be reproduced with the XML column in source SQL Server table size is 12M. Please open a support ticket with below information:

1. The source table creation DDL

2. How to make a 12MB XML in the table

3. Set souce_unload/target_load to Verbose, recreate the issue and upload the Diag Packages.

Regards,

John.