Capture XML filename when COPY XML file to Snowflake table

Question

I am trying to load an XML file from S3 to Snowflake table using COPY.

create table XML_TABLE (SRC VARIANT, FILENAME VARCHAR(100));

  copy into  XML_TABLE from
  '@STAGE/test.xml'
  file_format = (type = xml);

Is it possible to capture the filename also? because when I tried to add a filename column, I dont have an option in Copy to insert filename. I want a unique value for each file loaded. If Row_Number() can be used, please provide that information. TIA.

Answer 1

Try this:

copy into XML_TABLE from (
  select $1, metadata$filename from '@STAGE/test.xml'
) 
file_format=(type=xml);

You can also access METADATA$FILE_ROW_NUMBER this way.