I am in need of a way to import XML files that may contain data spanning multiple tables in a single database (MS SQL 2014). The files are received in a Zip file and stored in a folder on a cloud storage location (i.e. OneDrive, GoogleDrive, Dropbox, and so forth). The file names are constantly different as part of the name contains the current date and time (i.e. MyDataSite1-27APR2020_081507.ZIP). I have read several articles out here but, sadly, I am new to dealing with XML files and importing data into SQL via XML files. I have written several Visual Basic 2017 / 2019 programs; not that I need to use VB at all. My goal is to write a service that looks in a folder to see if there are any ZIP files to be processed. If there are files to be processed, then insert the data from the XML file(s) contained in each ZIP file from oldest ZIP file to latest ZIP file. The XML files are fully formatted meaning that they contain the table and field data for data going into a single destination MS SQL database. Though I have been programming for a good many year, I have sadly not needed to deal with XML files until now; besides, I have been tasked to start working on this as soon as I can. Thank you greatly for whatever you help with.
What I have tried:
I have not tried anything yet.