Wednesday, 31 July 2013

Import xml data from xml file into MS Access database file



DBXmlInfo.xml
<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2013-07-29T16:57:21">
 
  <Employees>
    <Employee>
        <EmpId>1001</EmpId>
        <Name>Ravi</Name>
        <Salary>111</Salary>
    </Employee>
    <Employee>
      <EmpId>1002</EmpId>
      <Name>Rahul</Name>
      <Salary>222</Salary>
    </Employee>
    <Employee>
      <EmpId>1003</EmpId>
      <Name>Chandan</Name>
      <Salary>333</Salary>
    </Employee>  
  </Employees>

  <studuents>
    <student>
      <StuId>2001</StuId>
      <Name>Ankit</Name>
      <RollNo>123</RollNo>
    </student>
    <student>
      <StuId>2002</StuId>
      <Name>Neetu</Name>
      <RollNo>456</RollNo>
    </student>
  </studuents>   
</dataroot>


C# Code
Microsoft.Office.Interop.Access.Application objAccess = new Microsoft.Office.Interop.Access.Application();

//objAccess.NewCurrentDatabase("c:\\TestDb.mdb", Microsoft.Office.Interop.Access.AcNewDatabaseFormat.acNewDatabaseFormatAccess2007);
objAccess.OpenCurrentDatabase("c:\\TestDb.mdb", false, "abc123");               

objAccess.ImportXML("C:\\DBXmlInfo.xml", Microsoft.Office.Interop.Access.AcImportXMLOption.acStructureAndData);

objAccess.CloseCurrentDatabase();
                objAccess.Quit(Microsoft.Office.Interop.Access.AcQuitOption.acQuitSaveAll);



Output
Above code will create two tables in TestDb.mdb database with like:
Employee
EmpId
Name
Salary
1001
Ravi
111
1002
Rahul
222
1003
Chandan
333

Student
StuId
Name
RollNo
2001
Ankit
123
2002
Neetu
456


No comments:

Post a Comment