Inserting from xml to datebase in vb

Question

I have xml that looks like this

<?xml version="1.0" standalone="yes"?>
<sdnList xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/sdnList.xsd">
  <sdnEntry>
    <uid>36</uid>
    <lastName>AEROCARIBBEAN AIRLINES</lastName>
    <sdnType>Entity</sdnType>
    <programList>
      <program>CUBA</program>
    </programList>
    <akaList>
      <aka>
        <uid>12</uid>
        <type>a.k.a.</type>
        <category>strong</category>
        <lastName>AERO-CARIBBEAN</lastName>
      </aka>
    </akaList>
    <addressList>
      <address>
        <uid>25</uid>
        <city>Havana</city>
        <country>Cuba</country>
      </address>
    </addressList>
  </sdnEntry>

And the code that i am using to insert starts like this

node_list = xml_doc.SelectNodes("//sdnEntry")
        For Each node In node_list
            If CheckElement("lastName", node) <> "" Then
                sql = "INSERT INTO us_sdnentry (uid_,lastname,sdntype) VALUES "
                sql &= "('" & node.SelectNodes("./uid").Item(0).InnerText & "','" & CheckElement("lastName", node) & "','" & node.SelectNodes("./sdnType").Item(0).InnerText & "'")"
                db_sql.RunSql(sql)
            End If
            sql = "".........

The problem is that when i am debugging, when my program executes it just skips the foreach, it dont go inside...

score 2 · Accepted Answer · edited May 23 '17 at 12:08

At first sight your code looks good, but when you put it in debug, you see that something is not working properly.

The main problem is the xml namespace. You can see how to perform XPath query with namespaces looking this: Problem running xpath query with namespaces.

Eventhough i love XElement in (System.Xml.Linq assembly) as Jurgen says, you can start facing your problem tring this code:

node_list = xml_doc.SelectNodes("//*[local-name() = 'sdnEntry']")
For Each node In node_list
     'Here your code '
Next

You can by-pass the namespace problem using the local-name property. So change your XPath query to:

//*[local-name() = 'yourtag']

And you'll see that now your program will start looping inside the For Each.

score 1 · Answer 2 · answered Jul 29 '16 at 10:02

You can use serialization to create little bid more clean and readable code.

<XmlType("sdnEntry")>
Public Class Entry
    <XmlElement("uid")> Public Property Id As String  
    <XmlElement("lastName")> Public Property LastName As String
    <XmlElement("sdnType")> Public Property SdnType As String
End Class

Then deserialize your xml to List(Of Entry)

Dim serializer As New XmlSerializer(GetType(List(Of Entry)),
                                    New XmlRootAttribute("sdnList"))
Dim entryList As List(Of Entry)
Using reader As new StreamReader("yourFile.xml")
    Dim deserialized = serializer.Deserialize(reader)
    entryList = DirectCast(deserialized, List(Of Entry))
End Using

Then use your list of object for inserting data in database

For Each item As Entry In entryList
    SaveEntry(item)
End For

Public Sub SaveEntry(data As Entry)
    Dim query As String = 
        "INSERT INTO us_sdnentry (uid_,lastname,sdntype) 
         VALUES
         (@Id, @LastName, @SdnType)"

    Dim parameters As SqlParameter() = 
    {
        New SqlParameter("@Id", data.Id),
        New SqlParameter("@LastName", data.LastName),
        New SqlParameter("@SdnType", data.SdnType),
    }

    Using conn As New SqlConnection("connectionString")
        Using command As New SqlCommand(query, conn)
            command.Parameters.AddRange(parameters)
            conn.Open()

            command.ExecuteNonQuery()
        End Using
    End Using
End Sub

Jürgen Steinblock · Answer 3 · 2016-07-29T09:36:26.687

Your program does not skip the For Each. node_list is empty (Check node_list.Count())

I don't know why this does not work because your XPath Query looks good. But since you are not making any query and just iterate over the nodes. I would suggest an alternative.

I am a big fan of the simplicity and usage of XElement (System.Xml.Linq assembly)

dim root = XElement.Load(fileName or stream or uri or reader)
For Each node in root.Elements("sdnEntry")
    If Not String.IsNullOrEmpty(node.Element("lastname")) Then
       ...
    End If
Next

Inserting from xml to datebase in vb

3 Answers3