How to retrieve all the values from an XML file when I have two titles in one XML document in a dinamic way?

Question

I need to use a dinamic way to get the value from My XML file.

The code i use to get the value was provide by @jdweng and i works...but i need a dynamic way.. because the problem with this way is if i change the name of tags .. i will need also to go at code to change the name.... but not if i have save the path in a table ..i just go at that table and i change here. That is also the solution

The code that i have for the moment :

Private Sub GetContinents()

    Const FILENAME As String = "C:test.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

Thank you for your help .

So the table should look like that or tables, because the first title does not have a low or high value, so this value will be take only where there is one .

So i have try this way....

Imports System.IO

Imports System.Xml

Module CdaParser Sub Main()

    Try
        Console.Write("Enter CDA path: ")

        Dim FilePath = Console.ReadLine()

        If Not File.Exists(FilePath)
            throw New Exception("Error! File does not exist.")
        End If

        'Load CDA
        Dim Document = New XmlDocument()

        Document.Load(FilePath)
        'Load CDA

        'Fetch and load namespaces
        Dim DocumentNamespaces = New XmlNamespaceManager(Document.NameTable)

        DocumentNamespaces.AddNamespace("xsi", "http://www.w3.org/2001/XMLSchema-instance")

        Dim XPathNavigator = Document.CreateNavigator()

        Dim Namespaces = XPathNavigator.GetNamespacesInScope(XmlNamespaceScope.All)

        If Namespaces IsNot Nothing

            For Each [Namespace] In Namespaces

                DocumentNamespaces.AddNamespace([Namespace].Key, [Namespace].Value)
            Next
        End If
        'Fetch and load namespaces

        'Rule definitions that must be take from db.
        Dim Rules = New Dictionary(Of String, String) From {
                {"First Name", "/*[local-name()='ClinicalDocument'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='recordTarget'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='patientRole'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='patient'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='name'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='given'][namespace-uri()='urn:hl7-org:v3']"},
                {"Last Name", "/*[local-name()='ClinicalDocument'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='recordTarget'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='patientRole'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='patient'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='name'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='family'][namespace-uri()='urn:hl7-org:v3']"},
                {"Test", "/*[local-name()='ClinicalDocument'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='structuredBody'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='section'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3'][4]/*[local-name()='section'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='entry'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='act'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='entryRelationship'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='observation'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='value'][namespace-uri()='urn:hl7-org:v3'][1]/@xsi:type"},
                {"Code", "/*[local-name()='ClinicalDocument'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='structuredBody'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='section'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='section'][namespace-uri()='urn:hl7-org:v3'][1]/*[local-name()='code'][namespace-uri()='urn:hl7-org:v3'][1]/@code"},
                {"Esame", "/*[local-name()='ClinicalDocument'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='structuredBody'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='section'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='component'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='section'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='text'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='table'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='thead'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='tr'][namespace-uri()='urn:hl7-org:v3']/*[local-name()='th'][namespace-uri()='urn:hl7-org:v3']"}}
        'Rule definitions

        '
        For Each Rule In Rules

            Dim Node = Document.SelectSingleNode(Rule.Value, DocumentNamespaces)

            Dim Value = ""

            If Node IsNot Nothing Then

                Value = Node.InnerText
            End If

            Console.WriteLine(Rule.Key + ": " + Value)
        Next


        'For Each rule In Rules
        '    Dim node = Document.SelectNodes(rule.Value, DocumentNamespaces)
        '    Dim value = ""
        '    If node IsNot Nothing Then
        '        value = node.innertext
        '    End If
        'Next


        Console.ReadLine()
    Catch Exception As Exception

        Console.WriteLine(Exception.Message)

        Console.ReadLine()
    End Try
End Sub

End Module

But also i have one like this second Xml

THE problem here it his that the table has more than rows the usually so for them i want to put one database like they were only separed for each row i wanna get -name-code-td value-low-hight-datetime.:

The result i got is :

Dim sections As List(Of XElement) = xe.Descendants("section").ToList() — jdweng, Nov 16 '18 at 10:06
You can use a for each to enumerate through the list. If you xml is not well formed then you have to use an XmlReader to parse the code. You do not have to edit the xml file. I can easily fix the code late today. — jdweng, Nov 16 '18 at 14:46

score 0 · Answer 1 · answered Nov 16 '18 at 10:33

Your XML is not valid, so I modified it to a well-formed XML:

<root>
    <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>fino a 0,35</td>
                            <td/>
                        </tr>
                    </tbody>
                </table>
                <footnote/>
                <paragraph>
                </paragraph>
                <!--Inizio Microbiologia sezione humane readable-->
                <!--Fine   Microbiologia sezione humane readable-->
            </text>
        </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>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/>
                <paragraph>
                </paragraph>
                <!--Inizio Microbiologia sezione humane readable-->
                <!--Fine   Microbiologia sezione humane readable-->
            </text>
        </section>
    </component>
</root>

You can use this code to parse the values.

Dim doc = New XmlDocument()
doc.Load("C:\Users\ShkelzenTarja\projekt\CDR_v3_1\CDR\test2.xml")
Dim components As XmlNodeList = doc.SelectNodes("root/component/section/text/table")

For c As Integer = 0 To components.Count - 1
    Dim th = components(c).SelectSingleNode("thead/tr").SelectNodes("th")
    Dim td = components(c).SelectSingleNode("tbody/tr").SelectNodes("td")

    For i As Integer = 0 To td.Count - 1
        Debug.WriteLine("component {0}: {1} => {2}", c, th(i).InnerText, td(i).InnerText)
    Next
Next

Result

component 0: Esame => Etanolo (urine)
component 0: Esito => < 0,01 g/l
component 0: Abnormal Flag => 
component 0: Unita di misura => 
component 0: Range di riferimento => fino a 0,35
component 0: Metodo => 
component 1: Esame => Creatininuria
component 1: Esito => 193.0
component 1: Abnormal Flag => 
component 1: Unita di misura => mg/dL
component 1: Range di riferimento => fino a 20: campione non idoneo
                                            (non utilizzabile ai fini medico legali)
                                            20 - 40: campione dubbio
                                            sup. a 40: campione idoneo

component 1: Metodo =>

Yes, `doc.SelectNodes` should work if you have more elements. Unless the file size gets hundreds of MBytes. — Wernfried Domscheit, Nov 16 '18 at 11:00
Cause i want you to have a look to my orginal xml.Because i get nothing and i load all the xml. — , Nov 16 '18 at 11:05
Maybe you should ask the hospital for a documentation of the XML structure. And perhaps read a book "XML for beginners" — Wernfried Domscheit, Nov 16 '18 at 12:02

jdweng · Accepted Answer · 2018-12-04T15:31:57.977

Here is how to read your xml if it is not well formed

Imports System.Data
Imports System.Xml
Imports System.Xml.Linq
Module Module1
    Const FILENAME As String = "c:\temp\test1.xml"
    Sub Main()
        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("ID", GetType(String))
        dt.Columns.Add("Set ID", GetType(String))
        dt.Columns.Add("Version", GetType(Integer))
        dt.Columns.Add("Document Display Name", GetType(String))
        dt.Columns.Add("Code Display Name", GetType(String))
        dt.Columns.Add("Code", GetType(String))
        dt.Columns.Add("Esname", 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"

        reader.ReadToFollowing("id", uri)
        Dim xId As XElement = XElement.ReadFrom(reader)
        Dim id As String = CType(xId.Attribute("extension"), String)
        reader.ReadToFollowing("setId", uri)
        Dim xSetId As XElement = XElement.ReadFrom(reader)
        Dim setId As String = CType(xSetId.Attribute("extension"), String)

        reader.ReadToFollowing("versionNumber", uri)
        Dim xVersion As XElement = XElement.ReadFrom(reader)
        Dim version As Integer = CType(xVersion.Attribute("value"), Integer)

        reader.ReadToFollowing("structuredBody", uri)

        While (Not reader.EOF)

            If reader.Name <> "component" Then
                reader.ReadToFollowing("component", uri)
            End If
            If Not reader.EOF Then

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

                Dim typeCode As XAttribute = documentComponent.Attribute("typeCode")
                If typeCode Is Nothing Then
                    Console.WriteLine("Error")
                Else
                    If CType(typeCode, String).Trim() <> "COMP" Then
                        Console.WriteLine("Error")
                    End If
                End If

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

                Dim section As XElement = documentComponent.Elements().Where(Function(x) x.Name.LocalName = "section").FirstOrDefault()

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

                    Dim body As XElement = codeComponent.Descendants().Where(Function(x) x.Name.LocalName = "tbody").FirstOrDefault()
                    Dim trs As List(Of XElement) = body.Elements().Where(Function(x) x.Name.LocalName = "tr").ToList()

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

                    Dim observations As List(Of XElement) = entry.Descendants().Where(Function(x) x.Name.LocalName = "observation").ToList()

                    If trs.Count <> observations.Count Then
                        Console.WriteLine("Error")
                    End If

                    Dim index As Integer = 0
                    For Each observation As XElement In observations
                        Dim data As New List(Of String)
                        data.AddRange({id, setId, version, documentDisplayName, codeDisplayName, code})
                        data.AddRange(trs(index).Elements.Where(Function(x) x.Name.LocalName = "td").Select(Function(x) CType(x, String)))


                        Dim low As XElement = observation.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 = observation.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 = observation.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))

                        dt.Rows.Add(data.ToArray())

                        index = index + 1
                    Next observation

                Next codeComponent

            End If
        End While
    End Sub

End Module

Are there only two tables or more? If more than two, are all the same format as the 2nd? — jdweng, Nov 20 '18 at 14:26
i made a fast test just for the firs one it worked.. i will make a test later and i will tell you more about it. Thank you a lot @jdweng — , Nov 21 '18 at 06:42
You are looking for a flat table results as your output. The problem is the repeated tags like section. I can do it but I'm really not sure how useful the results are going to be. I think it is better to look at which elements need to be dynamic and which elements can be fixed. I do not think the making the entire results dynamic makes a lot of sense with this xml file. There are too many levels or hierarchy in this xml file to make entire thing fit into a single flat datatable. — jdweng, Nov 27 '18 at 20:38
I am not sure if you understand...the value of the elemnts will be like it was...but the main idea is that the path to get to the element that we need to gain value ... should be stored in the database ... and then after we have defined the path we get that path from the database and we get the value of need element for example td path =root/component/section/text/table/tr/td --- this must be save in database and than i have already provide and example that do this.... get the path from database and than get the value of the elements. @jdweng — , Nov 27 '18 at 21:04
Why do you need the path when you have a unique code? You can always get the element by the code number. — jdweng, Nov 27 '18 at 21:34
Usng xml linq you can always get any node using descendants without have the full path. When you use the older xml net library (XmlDocument and XmlElement) it requires the full path to get any node. My preference is to always use xml linq (XDocument and XElement) and not bother with saving a full path. — jdweng, Nov 28 '18 at 09:49
yeah but i need this way....can you do it like this way. please... is not in my hand ...if it was for me ..it will be fine ...but i need the way i later said ...@jdweng — , Nov 28 '18 at 10:24
See following posting to parse mxl recursive : https://stackoverflow.com/questions/28976601/recursion-parsing-xml-file-with-attributes-into-treeview-c-sharp — jdweng, Nov 28 '18 at 10:47
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184397/discussion-between-aleksandra-stranova-and-jdweng). — , Nov 28 '18 at 11:39
Parse the date using ParseExact like I did in my code. The date is not a standard format and will not give correct results with other parsing methods. — jdweng, Nov 29 '18 at 20:10
It would not make any sense to make the entire xml output dynamic. It wouldn't give good results and have a lot of unnecessary information. I compared your original xml with the second xml file to see the differences. There are additional columns that can be added to the table but it would be better not to do dynamically because tags like referenceRange are arrays and each tag should be a new row in the table not new columns. — jdweng, Dec 04 '18 at 10:19
So the number of "tr" elements are always the same as the number of "component elements"? — jdweng, Dec 04 '18 at 11:25
Don't use debug view. Instead add break point at end of code. Then over over variable dt. Then click on down arrow and click Datatable Visualizer. — jdweng, Dec 04 '18 at 14:41

How to retrieve all the values from an XML file when I have two titles in one XML document in a dinamic way?

2 Answers2