Read XML and store in variables, ASMX, VB.NET

Question

I am trying to read an XML file from my server and write the values to a comma separated txt file. Can you help me? It is a webservice (ASMX) on a Windows Server 2012 R2.

I have tried many alternatives but none works for me, I cannot write to the TXT file. Could something be happening at the configuration level or ...?

Thank you very much in advance!

<?xml version="1.0" encoding="UTF-8"?>
<eventLog>
<event>
    <type>access1</type>
        <baseExtraData>
        <sample>Bone</sample>
        <age>65</age>
    </baseExtraData>
</event>
<event>
    <type>access2</type>
    <baseExtraData>
        <sample>Malow</sample>
        <age>11</age>
    </baseExtraData>
</event>
</eventLog>

<%@ WebService Language="VB" Class="WSIn" %>

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Data
Imports System.IO
Imports System.Xml

...

Dim doc As XmlDocument = New XmlDocument()
doc.Load("C:\inetpub\wwwroot\web\e2b59263af68.xml")
Dim root As XmlNode = doc.DocumentElement

??? ...

Where's the part of the code where you're trying to write to the text file? What doesn't work? — InbetweenWeekends, Apr 20 '20 at 13:47
The code to write in txt I have it working. What I need now is to be able to pass XML values to variables. Thank you! — Sergio, Apr 22 '20 at 06:38

score 0 · Answer 1 · answered Apr 20 '20 at 15:46

If your goal is to get these data to a CSV file then I would go over a DataTable. First read your XML into a DataTable, then export it to a CSV file. Your XML file is very near to the structure which could easily imported into a DataTable. The required structure is like this:

<root>
    <record>
        <field1>Some Data</field1>
        <field2>Some other Data</field2>
        <field3>42</field3>
        ...
    </record>
    <record>
        <field1>...</field1>
        <field2>...</field2>
        <field3>...</field3>
        ...
    </record>
</root>

So you should get rid of the unnecessary tag "baseExtraData". If you can influence the creating of the xml, very good. Else, some XSLT should do the job. Put this in a file with a name like "removebaseExtraData.xslt":

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
    <xsl:strip-space elements="*"/>

    <!-- Standard copy template: for all node where's no other rule: copy as they are -->
    <xsl:template match="node()|@*">
        <xsl:copy>
            <xsl:apply-templates select="node()|@*"/>
        </xsl:copy>
    </xsl:template>

    <!-- The node will be deleted and the included nodes drawn one level upwards -->
    <xsl:template match="baseExtraData">
        <xsl:apply-templates/>
    </xsl:template>
</xsl:stylesheet>

And here is the function to execute the XSLT:

Imports System.IO
Imports System.Xml

Public Sub XSLTTransform(ByVal XMLFile As String,
                         ByVal StylesheetFile As String,
                         Optional ByVal ResultFile As String = vbNullString)
    Dim XSLT As New Xsl.XslCompiledTransform(True)
    Dim SelfTransform As Boolean = (ResultFile = vbNullString)

    ' Load style sheet
    Try
        XSLT.Load(StylesheetFile)
    Catch ex As Exception
        Debug.Print(ex.Source & ": " & ex.Message)
        Exit Sub
    End Try

    ' Do the transform.
    Try
        If SelfTransform Then ResultFile = Path.GetTempFileName
        XSLT.Transform(XMLFile, ResultFile)
        If SelfTransform Then
            File.Delete(XMLFile)
            File.Move(ResultFile, XMLFile)
        End If
    Catch ex As Exception
        Debug.Print(ex.Source & ": " & ex.Message)
    End Try
End Sub

Now you should have something like this:

<?xml version="1.0" encoding="UTF-8"?>
<eventLog>
<event>
    <type>access1</type>
    <sample>Bone</sample>
    <age>65</age>
</event>
<event>
    <type>access2</type>
    <sample>Malow</sample>
    <age>11</age>
</event>
</eventLog>

The next step is easy:

Imports System.Xml

Dim XmlFile As XmlReader = XmlReader.Create(MyWorkfile, New XmlReaderSettings())
Dim DS As New DataSet

DS.ReadXml(XmlFile)
XmlFile.Close()
Dim MyDataTable As DataTable = DS.Tables(0)

Here MyWorkfile is the path of your (meanwhile modified) XML file and MyDataTable is your DataTable. This should now be easily exported to CSV. I haven't done this yet, so I cant give you one of my tested code. But there are plenty solutions in the net to do this task, maybe one of these links could help (I googled for "vb.net datatable to csv"):

DataTable to File

Most efficient way of converting a DataTable to CSV (C#)

http://www.devx.com/vb2themax/Tip/19703

Thanks Aranxo! The structure of the XML is that and at the moment I find it difficult to change it. It would be easier for me to have the values in variables to be able to write in TXT or insert in a database. I don't know if with some kind of loop to go through all the XML, etc. I hope you can help me. Thanks in advance. — Sergio, Apr 22 '20 at 06:42
Hello Sergio, well of course you could copy your XML file to another one (maybe called workfile) and change and import this one. But if you really want to read out all the data to variables and put them to a CSV file you could also do this. But this one would take some time for me, so maybe someone is faster. But I wont forget you... I will give you some ideas. — Aranxo, Apr 22 '20 at 16:19
Thank you very much @Aranxo ! I have managed to read the XML file, insert the value of the nodes into variables and write to TXT. Can i use it. Thank you! Now I have problems because sometimes in the XML I don't have all the nodes, and when there is one that is not, it does not write in the TXT nor does the function end. https://stackoverflow.com/questions/61432556/read-xml-different-number-of-nodes — Sergio, Apr 25 '20 at 21:04

score 0 · Answer 2 · answered Apr 22 '20 at 17:18

Ok, here's another one which maybe fits your needs better:

Imports System.IO
Imports System.Text
Imports System.Xml

Private Sub BtnImport_Click(sender As Object, e As EventArgs) Handles BtnImport.Click
    Dim DOC As New XmlDocument
    Dim SB As New StringBuilder
    Dim Line(2) As String

    SB.Append("type;sample;age")
    SB.Append(vbCrLf)

    DOC.Load(TxtXML.Text)
    Dim ElemList As XmlNodeList = DOC.DocumentElement.GetElementsByTagName("event")
    For Each Item As XmlNode In ElemList
        Line(0) = Item.ChildNodes(0).InnerText
        Line(1) = Item.ChildNodes(1).ChildNodes(0).InnerText
        Line(2) = Item.ChildNodes(1).ChildNodes(1).InnerText
        SB.Append(Join(Line, ";"))
        SB.Append(vbCrLf)
    Next

    Dim FS As New FileStream(TESTFOLDER & "\Test.csv", FileMode.Create)
    Dim SW As New StreamWriter(FS)

    SW.Write(SB.ToString)
    SW.Close()
End Sub

As you see, I built some form, which has a Textbox TxtXML which holds the path of your XML file and a button named BtnImport which start the action. Line(0) holds the type, Line(1) the sample and Line(2) the age. I tested this one and it works fine.

Well, this one is shorter, but its very specialized to your special task. The first solution is more flexible and fits for every XML file which has the required structure. — Aranxo, Apr 22 '20 at 17:27

Read XML and store in variables, ASMX, VB.NET

2 Answers2