Load XML file to variant data type column in Snowflake

[vvazza10]

Hi ! I am trying to load contents of a complete XML file to variant column in Snowflake. tFileInputXML component seem to read rows from the fields in XML. Please advise what components and design approach can i use to load complete XML to variant column of a table in snowflake?

[Anonymous]

Hello,

Are you able to insert an entire XML into Snowflake via tDBRow component?

Best regards

Sabrina

[vvazza10]

Hi Sabrina ! I was able to load the entire XML through tFileInputRaw with schema being document and and moved the file to S3 and loaded to snowflake table using tDBBulkExec. Is there any other way?

[Anonymous]

Hello,

Please try to insert an entire XML into Snowflake via tDBRow component and the sql is this with Prepared Statement :

"INSERT INTO <tablename> SELECT ?, PARSE_XML".

 Best regards

Sabrina

[vvazza10]

Thank you, Sabrina !

Is there any way to insert multiple rows to snowflake table from tXMLMap using user defined Insert sql query?

[vvazza10]

@Xiaodi Shi​ Hi Sabrina ! Could you tell me how to make this prepared statement? I tried the following statement and it failed -

"INSERT INTO TABLE_A SELECT ?, PARSE_XML"

The design flow is -

tfileInputRaw -> tDBRow

In tFileInputRaw, I chose "Stream the file"

[vvazza10]

@Xiaodi Shi​  - It worked 🙂

Design:

tFileInputRaw -

tDBRow -

[Anonymous]

Hello,

Please refer to this online component reference about: TalendHelpCenter: tSnowflakeRow

If you want to query the database using a prepared statement, you will select "Use PreparedStatement" option in advanced setting of tSnowflakeRow.

Best regards

Sabrina

[vvazza10]

Thanks, Sabrina ! I have used prepared statement and it worked fine.

But how do you generally recommend XML data to be loaded to Snowflake via Talend? Loading the XML as variant and then querying using snowflake FLATTEN queries or using Talend components and load like tFileInputXML -> tFileOutputDelimited (csv) -> Snowflake?

[Anonymous]

Hello,

It usually depends on your job requirement.

We have added support VARIANT type in tSnowflakeOutput and tSnowflakeInput since V 6.5.1 and 7.0.1.

Using tSnowlakeInput, tSnowflakeOutput - easy read and write without using complex queries.

If use tSnowflakeRow be aware of write restriction - Snowflake JDBC doesn't allow complex batch queries.

But it's possible to write in standard(not batch) mode. And it's preferable to use tSnowflakeOutput for write operations. On the other hand tSnowflakeRow is very good for reading data from Snowflake, even complex since you're using query.

Feel free to let us know if it helps.

Best regards

Sabrina