none
Parse data from .xml file using VBScript

    Question

  • I am new to programming, so wanted to ask a question, because this problem stopped my work fro a while... I have an .xml file (below is a fragment):

    <?xml version="1.0"?>
    <JAMonXML>
    ...................................
    <row rowID="3"> 
    <Modify>3</Modify> 
    <Label>/registration/app/main, ms.</Label> 
    <Hits>2</Hits> 
    <Avg>1,164</Avg> 
    <Total>2,327</Total> 
    <StdDev>13</StdDev> 
    <LastValue>1,154</LastValue> 
    <Min>1,154</Min> 
    <Max>1,173</Max> 
    <Active>0</Active> 
    <AvgActive>1</AvgActive> 
    <MaxActive>1</MaxActive> 
    <FirstAccess>5/8/13 2:21:40 PM</FirstAccess> 
    <LastAccess>5/8/13 2:21:41 PM</LastAccess> 
    <Enabled>true</Enabled> 
    <Primary>false</Primary> 
    <HasListeners>false</HasListeners> 
    </row>
    .......................
    </JAMonXML>

    I need to get a value, named LastValue: 1,154 in this time. I have a Windows computer, so I need to have a VBScript to do this and to get 1,154 as a result of running this script

    I will be very pleased, if you could help me solving this problem!

    I tried to write my own script, but there is a Error (line 1 char 12: Expected end of statement):

    Dim output As StringBuilder = New StringBuilder()

    Dim xDoc As MSXML.DOMDocument
    Set xDoc = New MSXML.DOMDocument
    xDoc.validateOnParse = False
    xDoc.Load("C:\Users\esyundyukov\Desktop\Qualification Work\jamonall-2.74\jamon.xml")
    ' Create an XmlReader
    Using reader As XmlReader = XmlReader.Create(New StringReader(xmlString))

        reader.ReadToFollowing("row")
        reader.MoveToFirstAttribute()
        Dim genre As String = reader.Value
        output.AppendLine("The value: " + Modify)
    End Using

    OutputTextBlock.Text = output.ToString()
    Thursday, May 09, 2013 10:57 AM

Answers

  • This is not VBScript, it is VB.NET. A correct VBScript would be:

    Option Explicit
    
    Dim DATA_FILE
    DATA_FILE = "C:\Temp\data.xml"
    
    Dim XDoc
    Dim XDocError
    Dim XDocNode
    Dim Output
    
    Set XDoc = CreateObject("MSXML.DOMDocument")
    
    If XDoc.Load(DATA_FILE) Then
      Set XDocNode = XDoc.SelectSingleNode("/JAMonXML/row[@rowID=3]/LastValue")    
      Output = XDocNode.Text
      WScript.Echo Output
    Else
      WScript.Echo "Could not load data file at " & DATA_FILE
    
      Set XDocError = xDoc.ParseError
      Output = "Your XML Document failed to load due the following error." & vbCrLf & _
               "Error #: " & XDocError.errorCode & ": " & XDocError.reason & _
               "Line #: " & XDocError.Line & vbCrLf & _
               "Line Position: " & XDocError.linepos & vbCrLf & _
               "Position In File: " & XDocError.filepos & vbCrLf & _
               "Source Text: " & XDocError.srcText & vbCrLf & _
               "Document URL: " & XDocError.url
      WScript.Echo Output
    End If

    But as you're posting in an Access forum: Do you need it as VBA (Visual Basic for Applications)?

    Option Compare Database
    Option Explicit
    
    Public Sub test()
      
      Const DATA_FILE As String = "C:\Temp\data.xml"
    
      Dim XDoc As Object 'MSXML.DOMDocument
      Dim XDocError As Object 'IXMLDOMParseError
      Dim XDocNode As Object 'IXMLDOMNode
        
      Dim Output As String
      
      Set XDoc = CreateObject("MSXML.DOMDocument") 'New DOMDocument
    
      If XDoc.Load(DATA_FILE) Then
        Set XDocNode = XDoc.SelectSingleNode("/JAMonXML/row[@rowID=3]/LastValue")
        Output = XDocNode.Text
        Debug.Print Output
      Else
        Debug.Print "Could not load data file at " & DATA_FILE
    
        Set XDocError = XDoc.ParseError
        Output = "Your XML Document failed to load due the following error." & vbCrLf & _
                 "Error #: " & XDocError.errorCode & ": " & XDocError.reason & _
                 "Line #: " & XDocError.Line & vbCrLf & _
                 "Line Position: " & XDocError.linepos & vbCrLf & _
                 "Position In File: " & XDocError.filepos & vbCrLf & _
                 "Source Text: " & XDocError.srcText & vbCrLf & _
                 "Document URL: " & XDocError.url
        Debug.Print Output
      End If
    
      Set XDocNode = Nothing
      Set XDocError = Nothing
      Set XDoc = Nothing
    
    End Sub

    Thursday, May 09, 2013 11:46 AM

All replies

  • Made a solution, that works for me:

    Dim xmlDoc, objNodeList, plot
    
    Set xmlDoc = CreateObject("Msxml2.DOMDocument")
    xmlDoc.load("C:\...\jamon.xml")
    Set objNodeList = xmlDoc.getElementsByTagName("LastValue")
    
    If objNodeList.length > 0 then
    For each x in objNodeList
    plot=x.Text
    msgbox plot
    Next
    Else
    msgbox chr(34) & "LastValue" & chr(34) & " not found."
    End If


    Thursday, May 09, 2013 11:18 AM
  • This is not VBScript, it is VB.NET. A correct VBScript would be:

    Option Explicit
    
    Dim DATA_FILE
    DATA_FILE = "C:\Temp\data.xml"
    
    Dim XDoc
    Dim XDocError
    Dim XDocNode
    Dim Output
    
    Set XDoc = CreateObject("MSXML.DOMDocument")
    
    If XDoc.Load(DATA_FILE) Then
      Set XDocNode = XDoc.SelectSingleNode("/JAMonXML/row[@rowID=3]/LastValue")    
      Output = XDocNode.Text
      WScript.Echo Output
    Else
      WScript.Echo "Could not load data file at " & DATA_FILE
    
      Set XDocError = xDoc.ParseError
      Output = "Your XML Document failed to load due the following error." & vbCrLf & _
               "Error #: " & XDocError.errorCode & ": " & XDocError.reason & _
               "Line #: " & XDocError.Line & vbCrLf & _
               "Line Position: " & XDocError.linepos & vbCrLf & _
               "Position In File: " & XDocError.filepos & vbCrLf & _
               "Source Text: " & XDocError.srcText & vbCrLf & _
               "Document URL: " & XDocError.url
      WScript.Echo Output
    End If

    But as you're posting in an Access forum: Do you need it as VBA (Visual Basic for Applications)?

    Option Compare Database
    Option Explicit
    
    Public Sub test()
      
      Const DATA_FILE As String = "C:\Temp\data.xml"
    
      Dim XDoc As Object 'MSXML.DOMDocument
      Dim XDocError As Object 'IXMLDOMParseError
      Dim XDocNode As Object 'IXMLDOMNode
        
      Dim Output As String
      
      Set XDoc = CreateObject("MSXML.DOMDocument") 'New DOMDocument
    
      If XDoc.Load(DATA_FILE) Then
        Set XDocNode = XDoc.SelectSingleNode("/JAMonXML/row[@rowID=3]/LastValue")
        Output = XDocNode.Text
        Debug.Print Output
      Else
        Debug.Print "Could not load data file at " & DATA_FILE
    
        Set XDocError = XDoc.ParseError
        Output = "Your XML Document failed to load due the following error." & vbCrLf & _
                 "Error #: " & XDocError.errorCode & ": " & XDocError.reason & _
                 "Line #: " & XDocError.Line & vbCrLf & _
                 "Line Position: " & XDocError.linepos & vbCrLf & _
                 "Position In File: " & XDocError.filepos & vbCrLf & _
                 "Source Text: " & XDocError.srcText & vbCrLf & _
                 "Document URL: " & XDocError.url
        Debug.Print Output
      End If
    
      Set XDocNode = Nothing
      Set XDocError = Nothing
      Set XDoc = Nothing
    
    End Sub

    Thursday, May 09, 2013 11:46 AM
  • Thanks for the help!

    I used your first example. :)

    But still I have a problem: rowID is not 3 all the time, it is changing every time I update my xml.

    How can I find this LastValue, using this constant tag

    <Label>/registration/app/main, ms.</Label>

    Thursday, May 09, 2013 12:41 PM
  • The key to the correct value is the XQuery in

     XDoc.SelectSingleNode("/JAMonXML/row[@rowID=3]/LastValue")

    Thus when you don't want rowID 3 then you need remove this filter and process it like this:

    Option Explicit
    
    Dim DATA_FILE
    DATA_FILE = "C:\Temp\data.xml"
    
    Dim XDoc
    Dim XDocError
    Dim XDocNode
    Dim XDocNodelist
    Dim Output
    
    Set XDoc = CreateObject("MSXML.DOMDocument")
    
    If XDoc.Load(DATA_FILE) Then
      Set XDocNodelist = XDoc.SelectNodes("/JAMonXML/row/LastValue")
      for each XDocNode in XDocNodelist
        Output = Output & vbCrLf & XDocNode.Text
      next 
      WScript.Echo Output
    Else
      WScript.Echo "Could not load data file at " & DATA_FILE
    
      Set XDocError = xDoc.ParseError
      Output = "Your XML Document failed to load due the following error." & vbCrLf & _
               "Error #: " & XDocError.errorCode & ": " & XDocError.reason & _
               "Line #: " & XDocError.Line & vbCrLf & _
               "Line Position: " & XDocError.linepos & vbCrLf & _
               "Position In File: " & XDocError.filepos & vbCrLf & _
               "Source Text: " & XDocError.srcText & vbCrLf & _
               "Document URL: " & XDocError.url
      WScript.Echo Output
    End If

    But now you need to be careful. SelectNodes() may return more than one result.

    Thursday, May 09, 2013 3:03 PM
  • Stefan, can I ask you for help, please!

    http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/7f8ffdb5-9f2c-4a97-ba85-84b405e501eb

    You are the only one, who can help me!

    With greatest regards,

    Emil

    Thursday, May 23, 2013 4:37 PM