Process to load XML file into Variant Column in Snowflake

[Derek2]

Looking for some assistance to design a process that loads a complete xml file into a variant column in Snowflake. The xml file exists in Azure Blob storage. Would also need to retrieve a few high level elements from the xml to put into the table along with the complete xml.

The target table would look something like this:

ProcessDatetime DATETIME - The Date the file was loaded into the table

ID1 INT - A unique ID value from the below the root node of the xml

TransactionType VARCHAR(100) - A character value from the root node of the xml that represent the transaction code

ID2 INT - Another unique ID value from the below the root node of the xml

Content VARIANT - Field for whole xml file.

It seems that I could use a bulk insert to load the file but not sure the best approach to parse file elements and load the file. Any assistance would be appreciated.

[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