XML file into MySQL Database - Help with .net script

[accelerator]

Hi All

I'm trying to write a VB.net script that will parse an AWIN XML file and insert its contents into a MySQL Products database. I am learning about XmlReader and XmlTextReader. So far, I have the code below, which will successfully display the contents of the xml file in the browser. However, I am a long way from getting it to insert into the database.

Any example scripts out there?

For a given <product></product> how do I retrieve the various tag values and put them into an SQL statement?

Do I add a SQL INSERT statement within the below loop?

Any comments and guidance appreciated.

Many thanks

Accelerator

__

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Load

Dim reader As XmlTextReader = New XmlTextReader(Server.MapPath("smartav.xml"))

'reads the first node, which is <?xml version="1.0" encoding="UTF-8"?>. We do not want to print this line; therefore, we will read it outside the loop and do nothing.
reader.Read()

While (reader.Read())

Response.Write(reader.Value)
Response.Write("<br />")

End While

End Sub
__

[Vivid]

I'm not a VB man so I can't help with your code but I did have a similar situation myself where I was loading a huge set of XML data in to a MySQL database. I tried many different pieces of software to achieve this and none did the trick so I tried Navicat. It actually didn't help with multiple XML files (like in a batch) but it did do a single file very nicely so I wrote to them and asked about multiple files/batch which they recently emailed me to say that had included in their latest release.

It might be worth a go.

[accelerator]

Thanks for your reply, I actually use Navicat at the moment, and I might look at their bulk upload if I can't do it with VB.net. My final objective is to get my MySQL product database to update automatically.

I need to code the following in VB.net:

1) Read through the XML file (AWIN version 1.0) until I find a <product> element.

2) Then loop through the child nodes and accumulate the relevant data for a database insert.

3) Do the database insert.

4) Move on to the next <product> element.

If there are any VB.net coders that could shed any light on how to code the above, particularly step 2) that would be much appreciated.

Rgds

Accelerator

[tbp]

Have you looked in the MSN Developer Center?

They offer a number of free tutorials and E Books which i`m sure will give you the answer to your problem.

Also do a search for "msdn vb.net xml" in Google which comes up with a load of results i`m sure that could help you!

[Donk]

Try xmlfiles.com

This page uses vbscript. I don't know how easy it would be to translate into into VB but it might give you a few pointers, especially if you can access the "Microsoft.XMLDOM".

[monkeyboy]

If you are using .NET 2 the DataSet object has a ReadXml function, which loads the XML from a file into data tables.

Dim objDs As New DataSet()

objDs.ReadXml("C:\path\to\xml\My.xml")

You can then loop through the tables in the resultant dataset.

[mcol]

if you're using mySql use the multiple insert format to stick the records into the database in batches. That'll cut your runtimes enormously.

[zinc]

Accel check this out http://samples.gotdotnet.com/quickst...adXMLFile.aspx