We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

How to parse the following XML using VBA code

Medium Priority
1,905 Views
Last Modified: 2013-11-26
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.
Comment
Watch Question

Author

Commented:
added tags
CERTIFIED EXPERT
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
CERTIFIED EXPERT

Commented:
that is exactly the output provided by the code above.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.