How to Import XML files into Access Database via C#?

Question

I have a test fixture that saves the results in XML files. From there, I can open up an Access database, import the file and append the contents to pre-existing tables. However, I'm trying to get this process automated.

So far with the help of a few tutorials, I've been able to open Access database files using C#. I could read the XML files, parse the data, and insert it into the tables , however, are there any functions/documentation to emulate the import wizard in Access with the XML file location as the argument ?

This is the structure of the XML file:

<root>
  <Test1>
    <Serial_x0020_Number>SerialNumber</Serial_x0020_Number>
    <Date>2015-03-03T07:13:28</Date>
    <Voltage1>7.620000</Voltage1>
    <Voltage2>7.620000</Voltage2>
    <Voltage3>7.620000</Voltage3>
    <Voltage4>7.620000</Voltage4>
    <Pass>N</Pass>
  </Test1>
  <Test2>
    <Serial_x0020_Number>SerialNumber</Serial_x0020_Number>
    <Date>2015-03-03T07:13:28</Date>
    <Voltage1>14.000000</Voltage1>
    <Voltage2>301.000000</Voltage2>
    <Voltage3>251.000000</Voltage3>
    <Voltage4>149.000000</Voltage4>
    <Voltage5>91.000000</Voltage5>

    <Pass>Y</Pass>
  </EFT_x0020_Waveform_x0020_Test>
</root>

The XML file can have anywhere from 1 to 150 entries with 1 to 12 tables, where each "Test" corresponds to a table and the entries in a test correspond to a column.

Tutorial I used to connect to the database: https://www.youtube.com/watch?v=MPBlsJpRFqY

Edit: I ended up parsing the XML and using some of the code Tim Morford posted. Thanks.

First try to parse your xml-file. There are multiple ways to accomplish this. A quick google would give you some options. — Stefan
– Stefan, Commented Mar 3, 2015 at 16:02
Are there any functions/documentation to emulate the import wizard in Access with the XML file location as the argument ? — Kilovolt
– Kilovolt, Commented Mar 3, 2015 at 16:13
I wouldn't know the answer to that question. Parsing xml to C# isn't that hard. — Stefan
– Stefan, Commented Mar 3, 2015 at 16:14
Correction: parsing xml in .net is hard, but it's a good way to learn about it :) — Stefan
– Stefan, Commented Mar 3, 2015 at 16:31

Community · Accepted Answer · 2017-05-23 10:09:50Z

2

First you need to parse the XML document:

XmlDocument xml = new XmlDocument();
xml.LoadXml(sResponse); //Your XML Document
foreach (XmlNode row in xml.ChildNodes) //Loop the child nodes
{
    Serial_x0020_Number = row.SelectSingleNode("Serial_x0020_Number").InnerText; //Get value of each node
    Date = row.SelectSingleNode("Date").InnerText;
    Voltage1 = row.SelectSingleNode("Voltage1").InnerText;
    Voltage2 = row.SelectSingleNode("Voltage2").InnerText;
    Voltage3 = row.SelectSingleNode("Voltage3").InnerText;
    Voltage4 = row.SelectSingleNode("Voltage4").InnerText;
    Passed= row.SelectSingleNode("Passed").InnerText;

    //Insert command here 
    string sSQL = "INSERT INTO TABLENAME(SN,Date,Voltage1, Voltage2,Voltage3,Voltage4,Passed) VALUES ('" + Date + "','" + Voltage1+ "','" + Voltage2+ "','" + Voltage3+ "','" + Voltage4+ "','" + PAssed + "');";
    //You will have to use a connection string and process the SQL in the typical way you are use to. 
}

You can also see this link for parsing XML How does one parse XML files?

edited May 23, 2017 at 10:09

CommunityBot

11 silver badge

answered Mar 3, 2015 at 16:19

Tim Morford

1011 gold badge1 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ryanyuyu Over a year ago

"Are you looking for this type of answer?" this statement is distracting. You'll know based on votes and comments. Also, any explanation for your code would also improve your answer.

Tim Morford Over a year ago

ryanyuyu I am new to this site just trying to help Kilovolt out, I don't know what is distracting or not.

ryanyuyu Over a year ago

It's good that you are trying to help out. It looks like you edited your answer and improved it. This effort suggests you'll easily fit into the SO community.

Collectives™ on Stack Overflow

How to Import XML files into Access Database via C#?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related