Parsing a PowerShell PSCustomObject XML with Excel VBS

Question

I spent hours trying to find my own solution, but failed :( All I want to do is Read in the XML and create a Muti-Layered-Array or ArrayList.

This is an example of the output from Powershell..

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>Selected.System.String</T>
      <T>System.Management.Automation.PSCustomObject</T>
      <T>System.Object</T>
    </TN>
    <MS>
      <I32 N="RefNum">1</I32>
      <S N="Name">MyVMName</S>
      <S N="Type">VM</S>
      <!-- Additional properties -->
    </MS>
  </Obj>
  <!-- Additional objects -->
</Objs>

This is one of many attempts but feel free to tear it apart.

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.Async = False

' Load the PowerShell XML content
xmlDoc.Load "your_powershell_xml_file.xml"

If xmlDoc.ParseError = 0 Then
    ' Use the namespace in XPath queries
    xmlDoc.setProperty "SelectionNamespaces", "xmlns:ps='http://schemas.microsoft.com/powershell/2004/04'"

    ' Select all PSCustomObject elements
    Set customObjects = xmlDoc.SelectNodes("//ps:Obj[TN/T='System.Management.Automation.PSCustomObject']")

    ' Iterate through PSCustomObject elements
    For Each customObject In customObjects
        ' Access properties within the PSCustomObject
        RefNum = customObject.SelectSingleNode("MS/I32[@N='RefNum']").Text
        Name = customObject.SelectSingleNode("MS/S[@N='Name']").Text
        Type = customObject.SelectSingleNode("MS/S[@N='Type']").Text

        ' Display the parsed properties
        WScript.Echo "RefNum: " & RefNum
        WScript.Echo "Name: " & Name
        WScript.Echo "Type: " & Type
    Next
Else
    ' Handle XML parsing error
    WScript.Echo "Error loading PowerShell XML: " & xmlDoc.ParseError.Reason
End If

Any Help to using VBS Reading in the XML Made from Powershell and populate a a Muti-Layered-Array or ArrayList. It fails at "xmlDoc.SelectNodes("//ps:Obj[TN/T='System.Management.Automation.PSCustomObject']")"

Answer 1

Each named element in an XPath query must use the appropriate namespace prefix.

Therefore, for instance, replace:

Set customObjects = xmlDoc.SelectNodes("//ps:Obj[TN/T='System.Management.Automation.PSCustomObject']")

with (note the ps: prefix in all element names):

Set customObjects = xmlDoc.SelectNodes("//ps:Obj[ps:TN/ps:T='System.Management.Automation.PSCustomObject']")

The same applies analogously to the subsequent .SelectSingleNode() calls.

To put it all together:

Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
xmlDoc.Async = False

' Load the PowerShell XML content
xmlDoc.Load "C:\path\to\your\file.xml"

If xmlDoc.ParseError = 0 Then
    ' Use the namespace in XPath queries
    xmlDoc.SetProperty "SelectionNamespaces", "xmlns:ps='http://schemas.microsoft.com/powershell/2004/04'"

    ' Select all PSCustomObject elements
    Set customObjects = xmlDoc.SelectNodes("//ps:Obj[ps:TN/ps:T='System.Management.Automation.PSCustomObject']")

    ' Iterate through PSCustomObject elements
    For Each customObject In customObjects
        ' Access properties within the PSCustomObject
        refNumAttr = customObject.SelectSingleNode("ps:MS/ps:I32[@N='RefNum']").Text
        nameAttr = customObject.SelectSingleNode("ps:MS/ps:S[@N='Name']").Text
        typeAttr = customObject.SelectSingleNode("ps:MS/ps:S[@N='Type']").Text

        ' Display the parsed properties
        Wscript.Echo ("RefNum: " & refNumAttr)
        Wscript.Echo ("Name: " & nameAttr)
        Wscript.Echo ("Type: " & typeAttr)
    Next
Else
    ' Handle XML parsing error
    Wscript.Echo ("Error loading PowerShell XML: " & xmlDoc.ParseError.Reason)
End If

Note:

• To run the code in Excel's Visual Basic for Application (VBA) code editor, with diagnostic output to the Immediate Window, replace Wscript.Echo with Debug.Print.