Member Avatar for Shkelzen_1

I need to Retrieve the values from a XML file i have seen many example but none of them is like the XML I have. My XML look like this

 <component>
                        <section>
                            <code code="31011" codeSystemName="Codifica Interna Laboratorio" displayName="Etanolo (urine)">
                                <!--TRASCODIFICA ANALISI NON DISPONIBILE-->
                            </code>
                            <text>
                                <paragraph>
                                </paragraph>
                                <table>
                                    <thead>
                                        <tr>
                                            <th>Esame</th>
                                            <th>Esito</th>
                                            <th>Abnormal Flag</th>
                                            <th>Unita di misura</th>
                                            <th>Range di riferimento</th>
                                            <th>Metodo</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <tr>
                                            <td>Etanolo (urine)</td>
                                            <td>&lt; 0,01 g/l</td>
                                            <td></td>
                                            <td></td>
                                            <td>fino a 0,35</td>
                                            <td />
                                        </tr>
                                    </tbody>
                                </table>
                                <footnote></footnote>
                                <paragraph>
                                </paragraph>
                                <!--Inizio Microbiologia sezione humane readable-->
                                <!--Fine   Microbiologia sezione humane readable-->
                            </text>
                            <entry typeCode="DRIV">
                                <!-- INIZIO MONO RISULTATO -->
                                <act classCode="ACT" moodCode="EVN">
                                    <code code="31011" codeSystemName="Codifica Interna Laboratorio" displayName="Etanolo (urine)">
                                        <!--TRASCODIFICA ANALISI NON DISPONIBILE-->
                                    </code>
                                    <statusCode code="completed" />
                                    <!--(INIZIO) GESTIONE MICROBIOLOGIA MONO RISULTATO -->
                                    <!--(FINE) GESTIONE MICROBIOLOGIA MONO RISULTATO -->
                                    <entryRelationship typeCode="COMP">
                                        <observation classCode="OBS" moodCode="EVN">
                                            <code code="31011" codeSystemName="Codifica Interna Laboratorio" displayName="Etanolo (urine)">
                                                <!--TRASCODIFICA RISULTATI NON DISPONIBILE-->
                                                <!--ANL_COMPLETED-->
                                            </code>
                                            <statusCode code="completed" />
                                            <effectiveTime value="20170216131204" />
                                            <value xsi:type="ST">&lt; 0,01 g/l</value>
                                            <!---->
                                            <referenceRange typeCode="REFV">
                                                <observationRange classCode="OBS" moodCode="EVN.CRT">
                                                    <value xsi:type="IVL_PQ">
                                                        <low value="0.00" />
                                                        <high value="0.35" />
                                                    </value>
                                                    <interpretationCode code="N" />
                                                </observationRange>
                                            </referenceRange>
                                            <referenceRange typeCode="REFV">
                                                <observationRange classCode="OBS" moodCode="EVN.CRT">
                                                    <value xsi:type="ST">fino a 0,35</value>
                                                    <interpretationCode code="N" />
                                                </observationRange>
                                            </referenceRange>
                                        </observation>
                                    </entryRelationship>
                                    <!-- VAL USED -->
                                </act>
                                <!-- FINE MONO RISULTATO -->
                            </entry>
                        </section>
                    </component>

As you can see is like a html table.I have create a table in database with those FIELDS name, and i need to put the values of the fields in database.

But first of all i need a way how to get the VALUES using visual basic and later on to see how to put the value on db. HELP ME PLEASE. THANK YOU.

My first test was to validate your XML. https://www.xmlvalidation.com/ failed what you posted so can you tell more about this apparently broken XML?

And now, story time. I too was asked to use XML files in a production app where the client would supply XML files. (Not) to my surprise Microsoft's XML reader blew up on their supplied XML files. After a few back and forths I decided that there was no way the client would correct their XML production method.

What happened next may shock you. (Obviously lifted from the web.) I dove in and just parsed out what I needed for our production app. Microsoft's XML reader was not used.

Assuming you correct the XML file and you know the name of the fields, you could do something like the following:

Imports System.IO

Public Class XMLreader
    Private Sub Populate_Click(sender As Object, e As EventArgs) Handles Populate.Click
        extractFields()
    End Sub
    Sub extractFields()
        Try
            Dim svFields() As String = {"code", "codeSystemName"}
            Dim vFieldType() As Type = {GetType(Int32), GetType(String)}
            Dim dt As New DataTable
            For i As Int32 = 0 To svFields.Length - 1
                dt.Columns.Add(New DataColumn(svFields(i), vFieldType(i)))
            Next
            Dim _assembly As System.Reflection.Assembly = System.Reflection.Assembly.GetExecutingAssembly
            Dim sr As Stream = _assembly.GetManifestResourceStream("XMLreader.XMLreader.xml")
            Using xtr As System.Xml.XmlReader = System.Xml.XmlReader.Create(sr)
                Do While xtr.Read
                    If xtr.IsStartElement Then
                        If xtr.HasAttributes Then
                            Dim nFields As Int32 = 0
                            Dim vRow(svFields.Length - 1)
                            While xtr.MoveToNextAttribute
                                Dim pos As Int32 = Array.IndexOf(svFields, xtr.Name)
                                If pos > -1 Then
                                    vRow(pos) = xtr.Value
                                    nFields += 1
                                    If nFields = svFields.Length Then
                                        Dim dr As DataRow = dt.NewRow
                                        dr.ItemArray = vRow
                                        dt.Rows.Add(dr)
                                    End If
                                End If
                            End While
                        End If
                    End If
                Loop
            End Using
            With DataGridView1
                .DataSource = dt
                .AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
            End With
        Catch ex As Exception

        End Try
    End Sub
End Class
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.