john8098
asked on
how do i read large xml file that root with id and some firld with array and then convert to database?
xml file as below about 100mb
<templates>
<template id="21000">
<id>21000</id>
<state>1</state>
<template_type>
<type_id>16</type_id>
<type_name>ZenCart Templates</type_name>
</template_type>
<screenshots_list>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-banner.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
</screenshot>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-m.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
<small_preview>1</small_pr eview>
</screenshot>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-rs.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
</screenshot>
<template>
<templates> ....
<templates>
<template id="21000">
<id>21000</id>
<state>1</state>
<template_type>
<type_id>16</type_id>
<type_name>ZenCart Templates</type_name>
</template_type>
<screenshots_list>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-banner.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
</screenshot>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-m.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
<small_preview>1</small_pr
</screenshot>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-rs.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
</screenshot>
<template>
<templates> ....
first problem is the code below code able to convert xml to database, but the root <template id="21000"> must be remove. but i am not able to remove it.
Public Sub Read_XML_File()
Dim ds As New DataSet
'Read File to Data Set
ds.ReadXml("t_info_dir.xml")
Dim TableCounter As Integer = 0
Dim dr As DataRow
Dim dataTableXml As Data.DataTable
Dim sqlCmd As String = ""
Dim ColCounter As Integer = 0
For TableCounter = 0 To ds.Tables.Count - 1
dataTableXml = ds.Tables(TableCounter)
For Each dr In dataTableXml.Rows
sqlCmd = ""
sqlCmd = "insert into [" & dataTableXml.TableName & "] ("
For i As Integer = 0 To dataTableXml.Columns.Count - 1
sqlCmd = sqlCmd + dataTableXml.Columns(i).ColumnName.ToString() + ","
Next
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ") values ("
For x As Integer = 0 To dataTableXml.Columns.Count - 1
Dim d As System.DateTime
Dim isDate As Boolean
If (DateTime.TryParse(dr(x).ToString(), d)) Then 'Checks If the Column is of Type Date
isDate = True
Else
isDate = False
End If
If (isDate) Then
sqlCmd = sqlCmd + "'" + Format(CDate(d.ToString), "dd/MMM/yyyy") + "',"
ElseIf InStr(dr(x).ToString, "'", CompareMethod.Text) <> 0 Then
sqlCmd = sqlCmd + "'" + dr(x).ToString().Replace("'", "''") + "',"
Else
sqlCmd = sqlCmd + "'" + dr(x) + "',"
End If
Next
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ");"
'Now Insert Query is prepared
'Run This query; Record will be inserted
Next
Next
End Sub
another problem is the xml file have multiple screenshot and multiple child, this will duplicate the field, that also create error to insert to database.
anyone expert guru pls help with the best solution and advice. thank!!!
ASKER
sorry, not the root. let say, <template id="21000"> is one of the record how am i gonna insert the xml data to database.
Do the tables already exist, or can you develop your own structure?
The relational way to deal with LISTS is to use two tables...
You would have one table for each template, each template having a unique id.
You would have another table for screenshots, with a column for the template id to which it belongs.
The relational way to deal with LISTS is to use two tables...
You would have one table for each template, each template having a unique id.
You would have another table for screenshots, with a column for the template id to which it belongs.
ASKER
yes, as i think b4, i will need create another table for screenshot, pls provide the code.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This was the XML i used, for reference
<templates>
<template id="21000">
<id>21000</id>
<state>1</state>
<template_type>
<type_id>16</type_id>
<type_name>ZenCart Templates</type_name>
</template_type>
<screenshots_list>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-banner.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
</screenshot>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-m.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
<small_preview>1</small_preview>
</screenshot>
<screenshot>
<uri>http://images.templatemonster.com/screenshots/21000/21000-rs.jpg</uri>
<filemtime>2008-08-28 08:08:20</filemtime>
</screenshot>
</screenshots_list>
</template>
</templates>
I am confused because the root of the XML document is <templates> not <template id="21000">.
do you mean you simply don't want to insert the info for template with id 21000?