MS SQL: importing XML data stored in a zip file when the file name changes often

Question

1.00/5 (1 vote)

See more:

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.

Posted 27-Apr-20 4:39am

Member 14814939

Add a Solution

Comments

Richard MacCutchan 27-Apr-20 11:08am

It is not exactly clear what your problem is. You can get a list of files in a folder by using the Directory class. You can extract all items from a zip file via the System.IO.Compression classes. And you can read XML files via the System.XML classes. Once you have the data from the individual XML files you should be able to post it inot your database.

Member 14814939 27-Apr-20 11:22am

Richard, It is not so much as being a problem. My concern is my own lack of knowledge of dealing with XML files; hence, reading them and writing the data into the existing tables in my SQL database. I have found much code online; however, a fair share of the examples are from many years ago. I am not saying that the code is unusable in the current time but instead I am saying that there may be some current code examples online somewhere that I could use to get this task completed in a timely manner. Again, my lack of knowledge in this particular area of reading XML files and inserting the data into the SQL database is a problem of mine and I am asking for some guidance. Thanks again.

Richard MacCutchan 27-Apr-20 12:07pm

See XML Documents and Data | Microsoft Docs[^]. You could use Google to search for articles on specific classes or class method referred to in that document.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)