Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1868
  • Last Modified:

How to parse the following XML using VBA code

I need to parse the following XML using VBA.
<node name="MachineName">
     <node name="name">
          <node name="TESTMGMT1" />
     </node>
     <node name="CPUs">
          <node name="row">
               <node name="name">
      <node name="0" />
               </node>
               <node name="percentReadyTime">
                     <metric-value value="24.31" unit="percent" timestamp="@@timestamp@@" />
               </node>
               <node name="percentSystemTime">
                    <metric-value value="0.07" unit="percent" timestamp="@@timestamp@@" />
               </node>
               <node name="usedHz">
      <metric-value value="190000000" unit="count" timestamp="@@timestamp@@"  />
               </node>
          </node>
     </node>
</node>
<node name="MachineName">
     <node name="name">
          <node name="TESTMGMT2" />
     </node>
     <node name="CPUs">
          <node name="row">
               <node name="name">
      <node name="0" />
               </node>
               <node name="percentReadyTime">
      <metric-value value="18.47" unit="percent" timestamp="@@timestamp@@"  />
               </node>
               <node name="percentSystemTime">
                      <metric-value value="0.01" unit="percent" timestamp="@@timestamp@@" />
               </node>
               <node name="usedHz">
      <metric-value value="73000000" unit="count" timestamp="@@timestamp@@" />
               </node>
          </node>
     </node>
</node>
I need to get the MachineName + node name and value which has metric-value:
ex data i need is:
MachineName : TESTMGMT1
percentReadyTime: 24.31
percentSystemTime: 0.07
usedHz: 190000000

MachineName : TESTMGMT2
percentReadyTime: 18.47
percentSystemTime: 0.01
usedHz: 73000000
Also if possible i want to write these values to Excel file in 2 colulmns using the same VBA code.
0
Abhi001
Asked:
Abhi001
  • 2
  • 2
1 Solution
 
Abhi001Author Commented:
added tags
0
 
Dave BrettCommented:
Assuming your test is in column A then this code wil dump your output to a csv file at C:\dumpee.csv
Example file  attached
Cheers
Dave


Sub ParseMe()
    Dim ws1 As Worksheet, regex, ReMC, RegM, rng1 As Range, i As Long, X()
    Dim fso, fsoFil
    Set ws1 = ActiveWorkbook.Sheets(1)
    Set rng1 = Range(Cells(Rows.Count, "A").End(xlUp), [a1])
    Set regex = CreateObject("vbscript.regexp")
    Set fso = CreateObject("scripting.filesystemobject")
    Set fsoFile = fso.createtextfile("c:\dumpee.csv", 2)
    X = rng1
    With regex
        .Global = True
        .ignorecase = True
        .MultiLine = True
    End With
 
    For i = 1 To UBound(X, 1)
     regex.Pattern = "[\r\s\t\n\""]+"
        tempStr = regex.Replace(X(i, 1), vbNullString)
        regex.Pattern = "<nodename=MachineName.+?=([a-zA-Z0-9]+)/.+?(\d+\.\d+).+?(\d+\.\d+).+?(\d+).+?</node>"
       
        If regex.test(tempStr) Then
            Set RegM = regex.Execute(tempStr)
            For Each RegM In RegM
                fsoFile.write "MachineName:," & RegM.submatches(0)
                fsoFile.writeline
                fsoFile.write "percentReadyTime:," & RegM.submatches(1)
                fsoFile.writeline
                fsoFile.write "percentSystemTime:," & RegM.submatches(2)
                fsoFile.writeline
                fsoFile.write "usedHz:," & RegM.submatches(3)
                fsoFile.writeline
            Next
        End If
    Next i
    Set fso = Nothing
    Set regex = Nothing
End Sub

Open in new window

parser.xls
0
 
Abhi001Author Commented:
Let us try this again without writing to Excel file.  For the XML posted in original Q
I need to get the MachineName + node name and value which has metric-value: I may have 1000 extra child nodes with/ wo metric-value. If some child nodes doesn't have metric value then skip that node.   All i need is to parse the xml and get below values:
ex data i need is:
MachineName : TESTMGMT1
percentReadyTime: 24.31
percentSystemTime: 0.07
usedHz: 190000000

MachineName : TESTMGMT2
percentReadyTime: 18.47
percentSystemTime: 0.01
usedHz: 73000000
0
 
Dave BrettCommented:
that is exactly the output provided by the code above.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now