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

enter image description here

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

jdweng · Accepted Answer · 2019-02-12 08:29:20Z

0

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

edited Feb 12, 2019 at 8:29

answered Feb 9, 2019 at 5:11

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

17 Comments

Muhammad Adeel Ahmed Over a year ago

Thanks jdewng. Is linq better option than just the normal loop or if i use Dataset inside .net. I tried to use ReadXML Function inside .net which brings the data in to Dataset but the datatables are created differently which is why i wanted to understand the XML structure. Can you please provide some bit of explanation to your code too. Thanks for your help

jdweng Over a year ago

The q in Linq stands for QUERY. So linq is really meant for queries. It is not designed to write to a new row in a datatable.

jdweng Over a year ago

Descendants gets an array of xml elements like "managedObject" . the I use Attribute to get the three attibutes : class, distName, and id. Next I get all the 'p' elements in "managedObject". Again each p item has an attibute and a value. I use CType to convert the XElement to a string.

Muhammad Adeel Ahmed Over a year ago

Thanks jdweng. I tried to follow your instructions but for loops doesn't trigger. There is no error also when i try to debug. managedObject element remains blank. I will be populating the data in to access table therefore using the Oledb Connection

Muhammad Adeel Ahmed Over a year ago

The XML also has header

|

Pᴇʜ · Accepted Answer · 2019-02-11 07:17:49Z

0

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

edited Feb 11, 2019 at 7:17

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

answered Feb 9, 2019 at 3:56

Santosh

12.4k4 gold badges43 silver badges74 bronze badges

1 Comment

Muhammad Adeel Ahmed Over a year ago

Thanks, I reckon this is done in Excel. I really appreciate for your kind help. I would like to understand some bits in the XML file if you can please explain. Is this any different type of XML? Is it possible to create XSD for this type of xml. If so then how it can be done. Any idea of how it can be done in VB.net ? Can you please also explain the code too for my understanding. Again many thanks for your help

Collectives™ on Stack Overflow

XML Data Structure with Multiple Elements

2 Answers 2

17 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

17 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related