XML Data Structure with Multiple Elements

Question

I am trying to understand the the structure of the following XML so that I can parse it in to Excel columns.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE raml SYSTEM 'raml20.dtd'>
<raml version="2.0" xmlns="raml20.xsd">
  <cmData type="actual">
    <header>
      <log dateTime="2019-02-08T15:05:41.000Z" action="created" appInfo="ActualExporter">InternalValues are used</log>
    </header>
    <managedObject class="AMLEPR" version="FL18_1711_06_1711_05" distName="PLMN-PLMN/MRBTS-502393/LNBTS-502393/LNCEL-10/AMLEPR-0" id="9075019">
      <defaults name="BLANK"/>
      <p name="cacHeadroom">0</p>
      <p name="deltaCac">2</p>
      <p name="maxCacThreshold">100</p>
      <p name="targetCarrierFreq">1400</p>
    </managedObject>
    <managedObject class="AMLEPR" version="FL18_1711_06_1711_05" distName="PLMN-PLMN/MRBTS-502393/LNBTS-502393/LNCEL-110/AMLEPR-0" id="9075022">
      <defaults name="BLANK"/>
      <p name="cacHeadroom">0</p>
      <p name="deltaCac">2</p>
      <p name="maxCacThreshold">100</p>
      <p name="targetCarrierFreq">2100</p>
    </managedObject>
    <managedObject class="AMLEPR" version="FL18_1711_06_1711_05" distName="PLMN-PLMN/MRBTS-502393/LNBTS-502393/LNCEL-114/AMLEPR-0" id="10755757">
      <defaults name="BLANK"/>
      <p name="cacHeadroom">0</p>
      <p name="deltaCac">2</p>
      <p name="maxCacThreshold">100</p>
      <p name="targetCarrierFreq">2300</p>
    </managedObject>
    <managedObject class="AMLEPR" version="FL18_1711_06_1711_05" distName="PLMN-PLMN/MRBTS-502393/LNBTS-502393/LNCEL-120/AMLEPR-0" id="9075025">
      <defaults name="BLANK"/>
      <p name="cacHeadroom">0</p>
      <p name="deltaCac">2</p>
      <p name="maxCacThreshold">100</p>
      <p name="targetCarrierFreq">2500</p>
    </managedObject>
      </cmData>
</raml>

I want to parse it in such a way that it looks like this

I want to understand how the child elements are defined in this XML. I see there are multiple elements with the same name "p" so whenever I try to import it in MS Access or even in VB.net Dataset then I get a column name with "p".

The code I am using to update Access is below:

Dim cs As String
        cs = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & Form1.db_txtbox.Text & "; Persist Security Info=False;"
        Dim con As New OleDb.OleDbConnection(cs)
        Using con
            Dim DelQuery As String
            Dim SqlQuery As String
            con.Open()
            DelQuery = "Delete * from LTE_AMLEPR"
            SqlQuery = "INSERT INTO LTE_AMLEPR (distName, MRBTS, LNBTS, LNCEL, AMLEPR, version, ID, cacHeadroom, deltaCac, maxCacThreshold, targetCarrierFreq) VALUES (@distName , @MRBTS, @LNBTS, @LNCEL, @AMLEPR, @version, @id, @cacHeadroom, @deltaCac, @maxCacThreshold, @targetCarrierFreq)"
            Dim da As New OleDbDataAdapter()
            Dim olecmd As OleDbCommand = New OleDbCommand(DelQuery, con)
            olecmd.ExecuteNonQuery()
            olecmd.Dispose()
            da.InsertCommand = New OleDbCommand(SqlQuery, con)
            da.InsertCommand.Parameters.Add("distName", OleDbType.VarChar, 100, "distName")
            da.InsertCommand.Parameters.Add("MRBTS", OleDbType.VarChar, 100, "MRBTS")
            da.InsertCommand.Parameters.Add("LNBTS", OleDbType.VarChar, 100, "LNBTS")
            da.InsertCommand.Parameters.Add("LNCEL", OleDbType.VarChar, 100, "LNCEL")
            da.InsertCommand.Parameters.Add("AMLEPR", OleDbType.VarChar, 100, "AMLEPR")
            da.InsertCommand.Parameters.Add("version", OleDbType.VarChar, 100, "version")
            da.InsertCommand.Parameters.Add("ID", OleDbType.VarChar, 100, "id")
            da.InsertCommand.Parameters.Add("cacHeadroom", OleDbType.VarChar, 20, "cacHeadroom")
            da.InsertCommand.Parameters.Add("deltaCac", OleDbType.VarChar, 20, "deltaCac")
            da.InsertCommand.Parameters.Add("maxCacThreshold", OleDbType.VarChar, 20, "maxCacThreshold")
            da.InsertCommand.Parameters.Add("maxCacThreshold", OleDbType.VarChar, 20, "targetCarrierFreq")
            da.Update(Dt)
        End Using

Can someone please advise and explain this structure of XML and how I can create the XSD for this type of XML.

Thanks

Answer 1

Sub parseXML()

    Dim strPath As String
    Dim strRow As String
    strPath = Application.GetOpenFilename

    Dim XDoc As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False
    XDoc.validateOnParse = False
    XDoc.Load (strPath)
    Set xObjDetails = XDoc.ChildNodes(0)
    Set xObject = xObjDetails.FirstChild
    For Each xObject In xObjDetails.ChildNodes
       strRow = xObject.Attributes(2).NodeValue & " | " & xObject.Attributes(3).NodeValue
        For Each xChild In xObject.ChildNodes
            strRow = IIf(xChild.Text <> "", strRow & " | " & xChild.Text, strRow)
        Next xChild
        Debug.Print strRow
    Next xObject

End Sub

---

Answer 2

try xml linq :

Imports System.Xml
Imports System.Xml.Linq
Imports System.Data
Module Module1

    Const FILENAME As String = "c:\temp\test.xml"
    Sub Main()
        Dim dt As New DataTable
        dt.Columns.Add("class", GetType(String))
        dt.Columns.Add("distName", GetType(String))
        dt.Columns.Add("id", GetType(String))
        dt.Columns.Add("cacHeadroom", GetType(String))
        dt.Columns.Add("deltaCac", GetType(String))
        dt.Columns.Add("maxCacThreshold", GetType(String))
        dt.Columns.Add("targetCarrierFreq", GetType(String))

        Dim settings As New XmlReaderSettings
        settings.DtdProcessing = DtdProcessing.Ignore
        Dim reader As XmlReader = XmlReader.Create(FILENAME, settings)
        Dim ns As XNamespace = XNamespace.Get("raml20.xsd")

        While (Not reader.EOF)
            If reader.Name <> "managedObject" Then
                reader.ReadToFollowing("managedObject")
            End If
            If Not reader.EOF Then
                Dim newRow As DataRow = dt.Rows.Add()
                Dim managedObject As XElement = XElement.ReadFrom(reader)

                newRow("class") = CType(managedObject.Attribute("class"), String)
                newRow("distName") = CType(managedObject.Attribute("distName"), String)
                newRow("id") = CType(managedObject.Attribute("id"), String)
                For Each p In managedObject.Elements(ns + "p")
                    Dim attrib As String = CType(p.Attribute("name"), String)
                    Dim value As String = CType(p, String)
                    newRow(attrib) = value

                Next p

            End If
        End While
    End Sub

End Module