How to recursively get nested properties from an XML file

I would like to recursively get properties in an XML document with PowerShell.  I find I can load up a document like the following example where $XML_FILE is a path to the file I want to work on.

$XML_Object = [xml](Get-Content $XML_File)

Once there I can easily get properties like the following example and its results.

$XML_Object | Get-Member -MemberType Properties

   TypeName: System.Xml.XmlDocument

Name        MemberType Definition                             
----        ---------- ----------                             
ShowPlanXML Property   System.Xml.XmlElement ShowPlanXML {get;}
xml         Property   System.String xml {get;set;} 

But as you can see the property may be something that itself has properties.  So I want to run Get-Member to find its properties.  This can go on for many levels and I don't know how deep it will go.  I've been trying to combine the use of Get-ChildItem -Recurse and Get-Property to no avail and think I must be missing something obvious.

Any advice or guidance on recursively getting nested properties would be we

March 22nd, 2013 7:21pm

To retrieve all the values of an xml file for whatever purpose you might better use the usual tools of the System.Xml assembly, for example as follows:

$xml = [XML](gc $XML_File)
$tmp = $xml.SelectNodes("//*")
$cnt = $tmp.Count

for ($i = 0; $i -lt $tmp.Count; $i++) {
    $tmp.Item($i).InnerText
}

Kind regards,

wizend


Free Windows Admin Tool Kit Click here and download it now
March 24th, 2013 11:23am

To retrieve all the values of an xml file for whatever purpose you might better use the usual tools of the System.Xml assembly, for example as follows:

$xml = [XML](gc $XML_File)
$tmp = $xml.SelectNodes("//*")
$cnt = $tmp.Count

for ($i = 0; $i -lt $tmp.Count; $i++) {
    $tmp.Item($i).InnerText
}

Kind regards,

wizend


March 24th, 2013 11:23am

Thank you Yan for taking the time to reply.  The three articles you referenced don't actually go any further than the example I put above.  In the three articles the Scripting Guy just loads the xml document and then manually investigates deeper and deeper as he finds something of interest. 

In my real life example I have a MS SQL query plan saved as XML output.  It is a very complex query and the query plan is almost 40,000 lines long.  So, what I'm really attempting to do is recurse through the whole thing programatically with PowerShell to find the nodes that are the highest cost in hopes of making troubleshooting these complex queries easier.  In any XML I'm trying to parse like this I have an unknown depth and at any point don't know if what shows up as a property will have more properties itself. 

If I do get this figured out I'll be sure to post the answer since I'm sure this would be helpful to MS SQL DBAs with big nasty query plans to parse.

March 25th, 2013 2:51pm

Let me provide a little more detail.  My real XML document is very long but I'll provide a small part of it here.

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="25.6162" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="16728.7" StatementText="Select *&#xD;&#xA;From #SalePurchaseData &#xD;&#xA;Inner Join v_BP_Integ_Tax_TrnHdr (NoLock) On #SalePurchaseData.XHdrID = v_BP_Integ_Tax_TrnHdr.XHdrID &#xD;&#xA;Inner Join v_BP_Integ_Tax_AcctDet (NoLock) On #SalePurchaseData.AcctDtlID = v_BP_Integ_Tax_AcctDet.AcctDtlID&#xD;&#xA;Inner Join AccountDetail (NoLock) On #SalePurchaseData.AcctDtlID = AccountDetail.AcctDtlID &#xD;&#xA;inner Join AccountingPeriod reportingperiod (NoLock) on reportingperiod.AccntngPrdID = @i_AccntngPrdID&#xD;&#xA;Inner join accountingperiod (NoLock) On accountingperiod.AccntngPrdID = accountdetail.AcctDtlAccntngPrdID&#xD;&#xA;Inner Join TransactionHeader (NoLock) on #SalePurchaseData.XHdrID = Transactionheader.XHdrID&#xD;&#xA;Inner Join v_UOMConversion NetGrossUOM (NoLock) On NetGrossUOM.FromUOM  = &#xD;&#xA; (select BaseVolumeUnitOfMeasureId from ConfigSystem where id = 1)&#xD;&#xA;And NetGrossUOM.ToUOM = v_BP_Integ_Tax_TrnHdr.ToUOMID&#xD;&#xA;Inner Join v_UOMConversion InvUOM (NoLock) On InvUOM.FromUOM = NetGrossUOM.FromUOM&#xD;&#xA;and InvUOM.ToUOM = Coalesce(v_BP_Integ_Tax_AcctDet.InvoiceUomID,AccountDetail.AcctDtlUOMID)&#xD;&#xA;" StatementType="SELECT" QueryHash="0xB54D2EF0A2868982" QueryPlanHash="0x10861F788BCF956E">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="4" MemoryGrant="3269664" CachedPlanSize="5616" CompileTime="4675" CompileCPU="4629" CompileMemory="65968">
            <RelOp AvgRowSize="33719" EstimateCPU="2.56162E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25.6162" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="16728.7">

            </RelOp>
            <ParameterList>
              <ColumnReference Column="@i_AccntngPrdID" ParameterRuntimeValue="(278)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

I've been able to manually test getting pieces of data I want as follows. Similar to the Scripting Guy Articles that Yan refrenced below.

$XML_Object | Get-Member -MemberType Properties

$XML_Object.ShowPlanXML | Get-Member -MemberType Properties
$XML_Object.ShowPlanXML.BatchSequence | Get-Member -MemberType Properties
$XML_Object.ShowPlanXML.BatchSequence.Batch | Get-Member -MemberType Properties
$XML_Object.ShowPlanXML.BatchSequence.Batch.Statements | Get-Member -MemberType Properties
$XML_Object.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple | Get-Member -MemberType Properties
$XML_Object.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple.QueryPlan | Get-Member -MemberType Properties

$Current_Node = $XML_Object.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple.QueryPlan.RelOp
$Grand_ETSC = $Current_Node.EstimatedTotalSubtreeCost | Where { [int]$Current_Node.NodeId -Eq 0}
$Grand_ETSC

While you can see how I can manually go through successive levels of this xml document and get some data I want out of it I'm trying to figure out now just how to recursively go through the whole thing.

I try the following function and function call but it only goes 2 levels deep and then errors.

Function Recurse-Levels
{
 Param
    (
 [Parameter(Position=0,Mandatory=1)]
 [object]$Some_Object
 )

 $Current_Level = $Some_Object
 $Properties = $Current_Level | Get-Member -MemberType Properties

 If ($Properties)
 {
  ForEach ($Property In $Properties)
  {
   $Child_Property = $Property.Name
   Write-Verbose $Child_Property
   $Child_Node = $XML_Object.$Child_Property
   Write-Verbose $Child_Node
   Recurse-Levels $Child_Node
  }
 }
}

Recurse-Levels $XML_Object

I see output like the following

VERBOSE: ShowPlanXML
VERBOSE: System.Xml.XmlElement
VERBOSE: BatchSequence
out-lineoutput : The OS handle's position is not what FileStream expected. Do not use a handle simultaneously in one Fi
leStream and in Win32 code or another FileStream. This may cause data loss.
    + CategoryInfo          : NotSpecified: (:) [out-lineoutput], IOException
    + FullyQualifiedErrorId : System.IO.IOException,Microsoft.PowerShell.Commands.OutLineOutputCommand

Perhaps I need to wrap my call in a Try/Catch so that if it tries to operate on something with no properties it doesn't error out.  Anyway, I think I am close but just missing something obvious.

Thanks to all who have looked at this and thought about it.

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2013 4:09pm

Hello anyone interested in this.  I'm very close with the following.  I think I will be able to get it to work from here.  I'll post up the final answer once I get it.  Of course, if anyone reading this has an elegant way to do this please chime in.

Function Recurse-Levels
{
 Param
    (
 [Parameter(Position=0,Mandatory=1)]
 [object]$Some_Object
 )

 $Current_Level = $Some_Object
 $Properties = $Current_Level | Get-Member -MemberType Properties

 If ($Properties)
 {
  ForEach ($Property In $Properties)
  {
   $Child_Property = $Property.Name
   Write-Verbose "The property name is $Child_Property"
   $Child_Node = $Current_Level.$Child_Property
   Write-Verbose "The child node is $Child_Node"
   
   Try
   {
    Recurse-Levels $Child_Node
   }
   Catch
   {
    $NULL
   }
  }
 }
}

Recurse-Levels $XML_Object

March 25th, 2013 5:27pm

Hi Wizend,

Thanks.  You got me onto the right track.  The following gets me where I want to be.  I have to add a bunch of code to this to do the actual work but I can see everything I need now.

Function Recurse_Levels
{
 Param
    (
 [Parameter(Position=0,Mandatory=1)]
 [object]$Some_Object
 ) 
 
 $Name = $Some_Object.Name
 $NodeType = $Some_Object.NodeType
 
 If ($Some_Object.HasChildNodes)
 {
  $Child_Nodes = $Some_Object.ChildNodes | Where {$_.NodeType -Eq "Element"}
  ForEach ($Node In $Child_Nodes)
  {
   Recurse_Levels $Node
  }
 }
}

Recurse_Levels $XML_Object

Free Windows Admin Tool Kit Click here and download it now
March 25th, 2013 9:52pm

Thank you, for sharing your solution with us. XML is always a challenge,

Have a nice day,

wizend

March 26th, 2013 6:26pm

hello Joe,

Thanks a lot for you share !

In some case, your recurcive function falls

$Child_Nodes=$Some_Object.ChildNodes| Where{$_.NodeType -Eq"Element"}

and I propose to add an if condition like this :

Function Recurse_Levels
 {
  Param
    (
  [Parameter(Position=0,Mandatory=1)]
  [object]$Some_Object
  )
 
  $Name = $Some_Object.Name
  $NodeType = $Some_Object.NodeType
  $Name
  If ($Some_Object.HasChildNodes)
  {
   $Child_Nodes = $Some_Object.ChildNodes | Where {$_.NodeType -Eq "Element"}
   if ($Child_Nodes -ne $null) {
    ForEach ($Node In $Child_Nodes)
    {
     Recurse_Levels $Node
    }
   }
  }
 }

 Have a nice day,

Philip

Free Windows Admin Tool Kit Click here and download it now
August 20th, 2015 11:48am

This topic is closed and answered.  Please post your question in a new topic and someone will try and help you understand how to do this correctly,
August 20th, 2015 1:14pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics