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, Mar 03 '15 at 16:02
Are there any functions/documentation to emulate the import wizard in Access with the XML file location as the argument ? — Kilovolt, Mar 03 '15 at 16:13
I wouldn't know the answer to that question. Parsing xml to C# isn't that hard. — Stefan, Mar 03 '15 at 16:14
Correction: parsing xml in .net is hard, but it's a good way to learn about it :) — Stefan, Mar 03 '15 at 16:31
possible duplicate of [Import XML file into Access](http://stackoverflow.com/questions/7436236/import-xml-file-into-access) — Ken White, Mar 03 '15 at 17:26
Not duplicate. It's about how to do it from Access and answer is really not that clear. Maybe there's something new, too, since it's from 2011. — Mikko Viitala, Mar 03 '15 at 17:52

score 2 · Answer 1 · edited May 23 '17 at 10:09

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?

"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. — ryanyuyu, Mar 03 '15 at 16:26
ryanyuyu I am new to this site just trying to help Kilovolt out, I don't know what is distracting or not. — Tim Morford, Mar 03 '15 at 16:30
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. — ryanyuyu, Mar 03 '15 at 16:50

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

1 Answers1