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.
