How to transform XML file to table in the database

Windows Forms General

Ccm1st

Hi Guys,

I have an XML file. I want to create an equivalent table of that XML in the database. Can you map the XML file to the table, so that whenever you update your XML file, the data in the database also gets updated and whenever data in database changes, the XML file gets updated





Top

Re: Windows Forms General How to transform XML file to table in the database

Guang-Ming Bian - MSFT

hi CCm1st

You can use Dataset.ReadXml and Dataset.WriteXml methods to solve your problem.

when you update your xml file, you can use Readxml, like the code below:

Code Snippet

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.Fill(ds);

ds.ReadXml(@"D;\yourxmlFile.xml");

da.Update(ds);

when you update database, you can use writexml , like the code below:

Code Snippet
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.Fill(ds);

ds.WriteXml(@"D;\yourxmlFile.xml");

I hope it helpful to you.





Top

Re: Windows Forms General How to transform XML file to table in the database

joeller

OK I am kind of confused. Let's see if I am following this.

You instantiate a new dataset and then a new dataadapter.

Then you call the DataAdapter Fill method with a parameter of the dataset, without setting any kind of connection to the database

In my situation I have a schema file which I would be having to read in before the xml file. So what I did was

Code Snippet

Dim Ds As New DataSet

Dim xmlDoc As New System.Xml.XmlDataDocument

Ds = xmlDoc.DataSet

Ds.ReadXmlSchema(Server.MapPath("App_Data\DEX403.xsd"))

xmlDoc.Load(Server.MapPath("App_Data\9004T.xml")

After adding a new DA instantiator and a and DA fill line like yours;

Code Snippet

Dim Ds As New DataSet

Dim xmlDoc As New System.Xml.XmlDataDocument

Dim daDSXML As New System.Data.OleDb.OleDbDataAdapter

daDSXML.Fill(Ds)

Ds = xmlDoc.DataSet

Ds.ReadXmlSchema(Server.MapPath("App_Data\DEX403.xsd"))

xmlDoc.Load(Server.MapPath("App_Data\9004T.xml")

I get the error message "The SelectCommand property has not been initialized before calling 'Fill'."

I can't make a select command because there is nothing in the database. The only things I want in the database are the things I am reading from the XML files. So unless I do something like "Select * from Sysobjects where 1 = 2" any select command is going to create false data in the dataset.





Top