Create an XML file from an access table

[patrickstrijdonck]

Hi All,

For a urgent work project I need to create an XML file from a c# program. In my access database there is a table called tblparcels.
it contains all parcel information.

not only the table information needs to be added, 2 fixed fields need to be added.

the XML should look like this:

<Shipment>
   <Userid>Myusername</Userid>
   <Password>Mypassword</Userid>
   <Parcel>
       <Tablefield1>Field1</Tablefield1>
   </Parcel>
   <Parcel>
       <Tablefield1>Field1</Tablefield1>
   </Parcel>
</Shipment>

Everything between <Parcel> </Parcel> is a record in that table.

I believe I need a datatable to start with, I know how to create that in c#.

If anyone can shoot me into the right direction for the XML part, would be very great

Happy Easter everyone!

 

[jmeckley]

query the records from the database
create an XmlDocument.
build the xml document based on the results.

var doc = new XmlDocument("shipment");
doc.CreateNode("username").Value = username;
doc.CreateNode("password").Value = password;
foreach(DataRow row in results.Rows)
{
  var node = doc.CreateNode("parecel");
  node.CreateNode("tablefield1").Value = row["column"];
}
return doc;

this is off the top of my head, so there may be errors.

also depending on the number of records in the access database you may need to find a better way to query the results. loading everything in to memory and then building the document could peg the processor.


Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[patrickstrijdonck]

Hi Jason,

Thanks for your reply,

I was looking on google for a very long time, After my post on here I found something of usefull, see code below;

string connectionstr = Properties.Settings.Default.VerzendDB1ConnectionString;

            OleDbConnection conn = new OleDbConnection(connectionstr);

            DataSet myDS = new DataSet("Shipment");

            string sql = "SELECT field1, field2, field3 FROM tblParcels";
            string sql1 = "SELECT Userid, Password FROM tbllogin";
            //Create a DataAdapter to load data from original data source to the DataSet
            OleDbDataAdapter myAdapter = new OleDbDataAdapter(sql, conn);
            OleDbDataAdapter myAdapter1 = new OleDbDataAdapter(sql1, conn);

            myAdapter1.Fill(myDS);
            myAdapter.Fill(myDS, "Parcel");

            //disconnect the database
            conn.Close();

            //Write out the schema that this DataSet created,
            //use the WriteXmlSchema method of the DataSet class
            myDS.WriteXmlSchema(Path.GetFullPath(@"C:\parcels.xsd"));


            // To write out the contents of the DataSet as XML,
            //use a file name to call the WriteXml method of the DataSet class
            myDS.WriteXml(Path.GetFullPath(@"C:\parcels.xml"), XmlWriteMode.IgnoreSchema);

This actually works very good, Only, The Login details are put in a child node like this below;

<Shipment>
  <table>
  <Userid>username</Userid>
  <Password>password</Password>
  </table>
   <Parcel>
    etc etc etc
   </Parcel>
</Shipment>

I just need to find a way to get rid of the <table> and </table>.
Any idea?

I thought at first to not give it a name,

            myAdapter1.Fill(myDS);
            myAdapter.Fill(myDS, "Parcel");

but it seems to put the name table in it.....