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_preview>
    </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!!!

Open in new window

john8098Asked:
Who is Participating?
 
Jens FiedererTest Developer/ValidatorCommented:
I would not try to load it as a dataset unless it actually WAS a saved dataset (and yours didn't work for me).

I'd load it as an XmlDocument and generate the sql THAT way.

Note that the exact code depends on the structure of the database, with correctly formatted XML (I had to provide close tags for a couple of your nodes) this code generates (of course you might want to, for example, format the date if you are using a date field instead of a string)

insert into [templates] (id, state) VALUES (21000, 1)
insert into [screenshots] (id, uri, filemtime) VALUES (21000, "http://images.templatemonster.com/screenshots/21000/21000-banner.jpg", "2008-08-28 08:08:20")
insert into [screenshots] (id, uri, filemtime, small_preview) VALUES (21000, "http://images.templatemonster.com/screenshots/21000/21000-m.jpg", "2008-08-28 08:08:20", "1")
insert into [screenshots] (id, uri, filemtime) VALUES (21000, "http://images.templatemonster.com/screenshots/21000/21000-rs.jpg", "2008-08-28 08:08:20")

Imports System.Text
Imports System.Xml
Imports System.Xml.XPath
 
Module Module1
 
    Sub Main()
        Read_XML_File()
    End Sub
 
    Public Sub Read_XML_File()
        Dim doc As New XmlDocument
        doc.Load("c:\doc\t_info_dir.xml")
 
        Dim templates As XmlNode = doc.FirstChild
 
        For Each template As XmlNode In templates.ChildNodes
            Dim id As String = "0"
            Dim screenshots As XmlNode = Nothing
            Dim sqlColumns As StringBuilder = New StringBuilder("insert into [templates] (")
            Dim sqlValues As StringBuilder = New StringBuilder(") VALUES (")
            Dim columncount As Integer = 0
            For Each column As XmlNode In template.ChildNodes
                If column.Name = "screenshots_list" Then
                    screenshots = column
                ElseIf column.Name = "template_type" Then
                    ' do whatever you want to do with that
                Else
                    If columncount <> 0 Then
                        sqlColumns.Append(", ")
                        sqlValues.Append(", ")
                    End If
                    columncount += 1
                    sqlColumns.Append(column.Name)
                    sqlValues.Append(column.InnerText)
                    If column.Name = "id" Then
                        id = column.InnerText
                    End If
                End If
            Next
            sqlColumns.Append(sqlValues.ToString)
            sqlColumns.Append(")")
            Dim primarySql As String = sqlColumns.ToString
            Trace.WriteLine(primarySql)
            ' execute this first
 
 
            If screenshots IsNot Nothing Then
                For Each screenshot As XmlNode In screenshots.ChildNodes
                    sqlColumns = New StringBuilder("insert into [screenshots] (id")
                    sqlValues = New StringBuilder(") VALUES (" & id)
                    For Each column As XmlNode In screenshot.ChildNodes
                        If column.Name = "screenshots_list" Then
                            screenshots = column
                        ElseIf column.Name = "template_type" Then
                            ' do whatever you want to do with that
                        Else
                            sqlColumns.Append(", ")
                            sqlValues.Append(", ")
                            sqlColumns.Append(column.Name)
                            sqlValues.Append("""" & column.InnerText & """")
                        End If
                    Next
                    sqlColumns.Append(sqlValues.ToString)
                    sqlColumns.Append(")")
                    Dim secondarySql As String = sqlColumns.ToString
                    ' execute this first
                    Trace.WriteLine(secondarySql)
 
                Next
            End If
        Next
 
    End Sub
End Module

Open in new window

0
 
Jens FiedererTest Developer/ValidatorCommented:
When you say " but the root <template id="21000"> must be removed", what do you mean by that?

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?

0
 
john8098Author Commented:
sorry, not the root. let say, <template id="21000"> is one of the record how am i gonna insert the xml data to database.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Jens FiedererTest Developer/ValidatorCommented:
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.
0
 
john8098Author Commented:
yes, as i think b4, i will need create another table for screenshot, pls provide the code.
0
 
Jens FiedererTest Developer/ValidatorCommented:
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>

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.