locked
Unable to index into an object of type System.Xml.XmlChildNodes. RRS feed

  • Question

  • Dear Experts,

    I'm having trouble while iterating through xml childnodes in tr(rows) I appreciate your help fixing this issue.

    Steps

    Step1 : I have an ADO.Net connection to connect to SQL server and run a query and store the datatable in resultsdatatable

    step2 : I am converting resultsdatatable to xml as shown below.

    [xml]$html2 = $resultsDataTable2 | ConvertTo-HTML -Property  SQLServer_CPU_Utilization,System_Idle_Process,Other_Process_CPU_Utilization,Total_Cpu_Utilization,Event_Time `
    -Fragment

    In the above code I am suppressing some of the system defined columns like rowstate, rowerror etc... which i don't need in my output. 

    My output of this XML is 

    <table><colgroup><col /><col /><col /><col /><col /></colgroup><tr><th>SQLServer_CPU_Utilization</th><th>System_Idle_Process</th><th>Other_Process_CPU_Utilization</th><th>Total_Cpu_
    Utilization</th><th>Event_Time</th></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:41:45 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:4
    0:44 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:39:44 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:38:44 PM</td></tr><tr><t
    d>0</td><td>99</td><td>1</td><td>1</td><td>8/22/2018 1:37:44 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:36:44 PM</td></tr><tr><td>0</td><td>97</td><td>
    3</td><td>3</td><td>8/22/2018 1:35:44 PM</td></tr><tr><td>0</td><td>99</td><td>1</td><td>1</td><td>8/22/2018 1:34:44 PM</td></tr><tr><td>0</td><td>97</td><td>3</td><td>3</td><td>8/2
    2/2018 1:33:44 PM</td></tr><tr><td>0</td><td>97</td><td>3</td><td>3</td><td>8/22/2018 1:32:44 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:31:44 PM</td><
    /tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:30:44 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:29:44 PM</td></tr><tr><td>0</td><td>9
    8</td><td>2</td><td>2</td><td>8/22/2018 1:28:44 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:27:44 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</
    td><td>8/22/2018 1:26:44 PM</td></tr><tr><td>0</td><td>97</td><td>3</td><td>3</td><td>8/22/2018 1:25:44 PM</td></tr><tr><td>0</td><td>97</td><td>3</td><td>3</td><td>8/22/2018 1:24:4
    4 PM</td></tr><tr><td>0</td><td>98</td><td>2</td><td>2</td><td>8/22/2018 1:23:44 PM</td></tr><tr><td>0</td><td>99</td><td>1</td><td>1</td><td>8/22/2018 1:22:44 PM</td></tr></table>

    Now while iterating through XML I am getting an error such as Unable to index into an object of type System.Xml.XmlChildNodes.

    Here is my for loop 

     

    for ($i=1;$i -le $html2.table.tr.count-1;$i++) 
    {
      if ($html2.table.tr[$i].td[3] -gt 80) 
      {
        $class = $html2.CreateAttribute("class")
        $class.value = 'red'
        $html2.table.tr[$i].childnodes[3].attributes.append($class) | out-null
      }
      Elseif ($html2.table.tr[$i].td[1] -lt 80) 
      {
        $class = $html2.CreateAttribute("class")
        $class.value = 'green'
        $html2.table.tr[$i].childnodes[3].attributes.append($class) | out-null
      }
     }

    Q1. Why am i getting an error while i am able to work with other queries like below are not throwing any errors and i am able to color the HTML based on service running or stopped. 

    [xml]html = Get-service | ConvertTO-HTML -Fragment 

    Q2. Is there any process i have to follow to convert datatables

    Kindly help with this issue 


    • Edited by info.sqldba Wednesday, August 22, 2018 11:19 AM
    Wednesday, August 22, 2018 9:01 AM

All replies

  • First fix your post.  Post code correctly using the code posting tool provided.


    \_(ツ)_/

    Wednesday, August 22, 2018 9:18 AM
  • The simple answer is to use XPath to retrieve and alter the required rows.

     How are you creating the datatable?

    $rdr = $cmd.ExecuteReader()
    $dt = [System.Data.DataTable]::New()
    $dt.Load($rdr)
    [cml]$html = $dt | ConvertTo-Html
    $html.SelectNodes('//tr[td="111"]')

    Selects all nodes where a child has "111" for text.



    \_(ツ)_/


    • Edited by jrv Wednesday, August 22, 2018 9:40 AM
    Wednesday, August 22, 2018 9:23 AM
  • I apologize for my filthy post without proper indentation. 

    What exactly i am doing over here is the resultset which i obtain from SQL to be displayed as HTML report so in this code I am converting the datatable to xml where i can iterate through each row and insert my css color either red or green based on condition. while iterating through converted xml i am getting an error. Could you please give me an example making with Xpath. 


    Wednesday, August 22, 2018 9:31 AM
  • Please fix your original post.  Post XML as XML using the code posting tool.  As posted it is badly broken and cannot be copied. 

    \_(ツ)_/

    Wednesday, August 22, 2018 9:44 AM
  • $html.SelectNodes('//tr[td="111"]') |
        ForEach-Object{
            $c = $html.CreateAttribute('class')
            $c.Value = 'red'
            $_.Attributes.Append($c)
        }
    

    See the following: http://tech-comments.blogspot.com/2012/07/powershell-dynamically-color-posh.html


    \_(ツ)_/

    Wednesday, August 22, 2018 9:55 AM
  • I have edited the post
    Wednesday, August 22, 2018 11:20 AM