Odtnhj

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 .

 <title>TOSSICOLOGIA D'ABUSO</title>
 <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>
 <component>
 <section>
 <code code="32000" codeSystemName="Codifica Interna Laboratorio" displayName="Creatininuria">
 <!--TRASCODIFICA ANALISI NON DISPONIBILE-->
 </code>
 <text>
 <paragraph>
 <content ID="ANLNOTE---2-2">Prova autenticità campione droghe</content>
 </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>Creatininuria</td>
 <td>193.0</td>
 <td></td>
 <td>mg/dL</td>
 <td>fino a 20: campione non idoneo
 (non utilizzabile ai fini medico legali)
 20 - 40: campione dubbio
 sup. a 40: campione idoneo
 </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="32000" codeSystemName="Codifica Interna Laboratorio" displayName="Creatininuria">
 <!--TRASCODIFICA ANALISI NON DISPONIBILE-->
 </code>
 <statusCode code="completed" />
 <!--(INIZIO) GESTIONE MICROBIOLOGIA MONO RISULTATO -->
 <!--(FINE) GESTIONE MICROBIOLOGIA MONO RISULTATO -->
 <entryRelationship typeCode="SUBJ">
 <act classCode="ACT" moodCode="EVN">
 <code code="48767-8" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="Annotation Comment" />
 <text>
 <reference value="ANLNOTE---2-2" />
 </text>
 </act>
 </entryRelationship>
 <entryRelationship typeCode="COMP">
 <observation classCode="OBS" moodCode="EVN">
 <code code="32000" codeSystemName="Codifica Interna Laboratorio" displayName="Creatininuria">
 <!--TRASCODIFICA RISULTATI NON DISPONIBILE-->
 <!--ANL_COMPLETED-->
 </code>
 <statusCode code="completed" />
 <effectiveTime value="20170216121035" />
 <value xsi:type="PQ" value="193.0" unit="mg/dL" />
 <referenceRange typeCode="REFV">
 <observationRange classCode="OBS" moodCode="EVN.CRT">
 <value xsi:type="IVL_PQ">
 <low value="40.0" unit="mg/dL" />
 <high value="99999.0" unit="mg/dL" />
 </value>
 <interpretationCode code="N" />
 </observationRange>
 </referenceRange>
 <referenceRange typeCode="REFV">
 <observationRange classCode="OBS" moodCode="EVN.CRT">
 <value xsi:type="ST">fino a 20: campione non idoneo
 (non utilizzabile ai fini medico legali)
 20 - 40: campione dubbio
 sup. a 40: campione idoneo
 </value>
 <interpretationCode code="N" />
 </observationRange>
 </referenceRange>
 </observation>
 </entryRelationship>
 <!-- VAL USED -->
 </act>
 <!-- FINE MONO RISULTATO -->
 </entry>
 </section>

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.
So this is the image that tell the value i need are under tag ,,td'' at the XML.
But the table ate db should look like this.... the value of the part of XML is the first in the second table.

For this part of the code i can use

 Dim path As String = "C:UsersShkelzenTarjaprojektCDR_v3_1CDRtest2.xml" ' "put path to xml file here"
 Dim xe As XElement
 xe = XElement.Load("C:UsersShkelzenTarjaprojektCDR_v3_1CDRtest2.xml")
 Dim sectn As XElement = (From el In xe...<code> Select el Take 1).FirstOrDefault

 If sectn IsNot Nothing Then 'did we get a code
 'yes
 sectn = sectn.Parent 'the section
 '
 End If
 If sectn IsNot Nothing Then 'did we get a section
 'yes
 For Each tr As XElement In sectn...<table>.<tbody>.<tr>
 For Each td As XElement In tr.<td>
 Debug.WriteLine(td.Value)
 Next
 Next

 End If

but i have some question :

1- the diff of path and xe = XElement.Load

2- Select el Take 1).FirstOrDefault ... how can i change this in order to get all the values the document has and not only the first one .

3- should i use the same sintax if my xml has two title because in fact it has as you can see at the photo .
Thank you for your help @dbasnett

MY all Xml is here :
enter link description here

The XML example was not formatted correctly. This might give you an idea

For the production code use

 Dim path As String = "put path to xml file here"
 Dim xe As XElement
 xe = XElement.Load(path) 'load the xml from file

Because this is VB you can use a literal to test with. I did my best to fix the XML

 Dim xe As XElement

 xe = <title>TOSSICOLOGIA D'ABUSO
 <component>
 <section>
 <code code="31011" codeSystemName="Codifica Interna Laboratorio" displayName="Etanolo (urine)"></code>
 <text>
 <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>
 </text>
 </section>
 </component>
 <section>
 <code code="32000" codeSystemName="Codifica Interna Laboratorio" displayName="Creatininuria"></code>
 <text>
 <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>Creatininuria</td>
 <td>193.0</td>
 <td></td>
 <td>mg/dL</td>
 <td>fino a 20: campione non idoneo
 (non utilizzabile ai fini medico legali)
 20 - 40: campione dubbio
 sup. a 40: campione idoneo
 </td>
 <td/>
 </tr>
 </tbody>
 </table>
 </text>
 </section>
 </title>

Next find one of the tables. I will use the code node to do that

 Dim sectn As XElement = (From el In xe...<code> Where el.@code = "32000" Select el Take 1).FirstOrDefault

 If sectn IsNot Nothing Then 'did we get a code
 'yes
 sectn = sectn.Parent 'the section
 '
 End If

Just some ideas. Hope it helps.
Once we have a section we can iterate the rows in the body

 If sectn IsNot Nothing Then 'did we get a section
 'yes
 For Each tr As XElement In sectn...<table>.<tbody>.<tr>
 For Each td As XElement In tr.<td>
 Debug.WriteLine(td.Value)
 Next
 Next
 '
 End If

Take a look at the following link

LINQ to XML link

The solution

Private Sub GetContinents()

 Const FILENAME As String = "C:UsersShkelzenTarjaprojektCDR_v3_1CDRtest.xml"

 Dim settings As XmlReaderSettings = New XmlReaderSettings()

 settings.ConformanceLevel = ConformanceLevel.Fragment

 Dim reader As XmlReader = XmlReader.Create(FILENAME, settings)


 Dim dt As DataTable = New DataTable()
 dt.Columns.Add("Display Name", GetType(String))
 dt.Columns.Add("Code", GetType(String))
 dt.Columns.Add("Esame", GetType(String))
 dt.Columns.Add("Esito", GetType(String))
 dt.Columns.Add("Abnormal Flag", GetType(String))
 dt.Columns.Add("Unita Di Misura", GetType(String))
 dt.Columns.Add("Range Di Riferimento", GetType(String))
 dt.Columns.Add("Metoda", GetType(String))
 dt.Columns.Add("Low", GetType(Decimal))
 dt.Columns.Add("High", GetType(Decimal))
 dt.Columns.Add("Time", GetType(DateTime))


 Dim uri = "urn:hl7-org:v3"

 While (Not reader.EOF)

 If reader.Name <> "section" Then

 reader.ReadToFollowing("section", uri)
 End If
 If Not reader.EOF Then

 Dim section As XElement = CType(XElement.ReadFrom(reader), XElement)

 Dim xCode As XElement = section.Descendants().Where(Function(x) x.Name.LocalName = "code").FirstOrDefault()
 Dim displayName As String = CType(xCode.Attribute("displayName"), String)

 For Each xComponent As XElement In section.Elements().Where(Function(x) x.Name.LocalName = "component")
 Dim xEsame As XElement = xComponent.Descendants().Where(Function(x) x.Name.LocalName = "code").FirstOrDefault()
 Dim code As String = CType(xEsame.Attribute("code"), String)

 Dim xBody As XElement = xComponent.Descendants().Where(Function(x) x.Name.LocalName = "tbody").FirstOrDefault()
 Dim data As New List(Of String)
 data.AddRange(displayName, code)
 data.AddRange(xBody.Descendants().Where(Function(x) x.Name.LocalName = "td").Select(Function(x) CType(x, String)))

 Dim entry As XElement = section.Descendants().Where(Function(x) x.Name.LocalName = "entry").FirstOrDefault()

 Dim low As XElement = entry.Descendants().Where(Function(x) x.Name.LocalName = "low").FirstOrDefault()
 If low Is Nothing Then
 data.Add(Nothing)
 Else
 data.Add(Decimal.Parse(CType(low.Attribute("value"), String)))
 End If
 Dim high As XElement = entry.Descendants().Where(Function(x) x.Name.LocalName = "high").FirstOrDefault()
 If high Is Nothing Then
 data.Add(Nothing)
 Else
 data.Add(Decimal.Parse(CType(high.Attribute("value"), String)))
 End If

 Dim effectiveTime As XElement = entry.Descendants().Where(Function(x) x.Name.LocalName = "effectiveTime").FirstOrDefault()
 Dim dateStr As String = CType(effectiveTime.Attribute("value"), String)
 data.Add(DateTime.ParseExact(dateStr, "yyyyMMddHHmmss", System.Globalization.CultureInfo.InvariantCulture))

 'For i = 0 To data.Count - 1
 Insert_CDA_Data(data)
 ' i = i + 11
 'Next
 'dt.Rows.Add(data.ToArray())
 'Debug.WriteLine(dt)

 Next xComponent

 End If
 End While

End Sub

Private Function Insert_CDA_Data(ByVal data As List(Of String))
 Dim esito As Boolean = True

 Try
 Using scope As New TransactionScope()

 operations.INSERT_CDA(ucDataCapture(data))
 scope.Complete()
 End Using

 Catch ex As Exception
 esito = False
 Dim log As New Log
 log.writeLog("Cda", ex)
 End Try

 Return esito
End Function

Private Function ucDataCapture(ByVal data As List(Of String)) As CDA

 Dim CDA_D As New CDA

 Try
 CDA_D.codeProperty = data(1)
 CDA_D.nameProperty = data(0)
 CDA_D.esameProperty = data(2)
 CDA_D.esitoProperty = data(3)
 CDA_D.abnormalFlagProperty = data(4)
 CDA_D.unitaDiMisuraProperty = data(5)
 CDA_D.rangeDiRiferimentoProperty = data(6)
 CDA_D.metodaProperty = data(7)
 If data(8) IsNot Nothing Then
 CDA_D.lowProperty = data(8)
 Else
 CDA_D.lowProperty = ""
 End If
 If data(9) IsNot Nothing Then
 CDA_D.highProperty = data(9)
 Else
 CDA_D.highProperty = ""
 End If

 CDA_D.effectiveTimeProperty = data(10)
 Catch ex As Exception
 Dim log As New Log
 log.writeLog("Cda ", ex)
 End Try
 Return CDA_D

End Function