Go Premium for a chance to win a PS4. Enter to Win

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

Read data from XML

Hi guys,

I need help to make some script in vbscript or powershell. To read data from xml named "Solutions.xml" and list the following fields in excel file:

SolutionName:           LastOperationResult      LastOperationTime


And order by LastOperationTime

<Solutions Count="222">
   <Solution Name="3.5.config.wsp">
      <Id>9d2ae790-08c9-4088-ac32-dc8814531065</Id>
      <File>3.5.Config.wsp</File>
      <Deployed>TRUE</Deployed>
      <WebApplicationSpecific>FALSE</WebApplicationSpecific>
      <ContainsGlobalAssembly>TRUE</ContainsGlobalAssembly>
      <ContainsCodeAccessSecurityPolicy>FALSE</ContainsCodeAccessSecurityPolicy>
      <LastOperationResult>DeploymentSucceeded</LastOperationResult>
      <LastOperationTime>9/19/2011 1:15 PM</LastOperationTime>
   </Solution>
   <Solution Name="dp.sharepoint.workflow.wsp">
      <Id>7a78b738-6d4f-11dc-833e-02d356d89593</Id>
      <File>DP.Sharepoint.Workflow.wsp</File>
      <Deployed>TRUE</Deployed>
      <WebApplicationSpecific>TRUE</WebApplicationSpecific>
      <ContainsGlobalAssembly>TRUE</ContainsGlobalAssembly>
      <ContainsCodeAccessSecurityPolicy>FALSE</ContainsCodeAccessSecurityPolicy>
      <Deployment WebApplication="http://test.com/" />
      <LastOperationResult>DeploymentSucceeded</LastOperationResult>
      <LastOperationTime>9/19/2011 1:16 PM</LastOperationTime>
   </Solution>
   <Solution Name="exportwsp.wsp">
      <Id>de7a5850-03b9-44a6-a59c-31bceb1d9605</Id>
      <File>exportwsp.wsp</File>
      <Deployed>TRUE</Deployed>
      <WebApplicationSpecific>FALSE</WebApplicationSpecific>
      <ContainsGlobalAssembly>TRUE</ContainsGlobalAssembly>
      <ContainsCodeAccessSecurityPolicy>FALSE</ContainsCodeAccessSecurityPolicy>
      <LastOperationResult>DeploymentSucceeded</LastOperationResult>
      <LastOperationTime>9/21/2011 3:15 PM</LastOperationTime>
   </Solution>
</Solutions>

Open in new window



Thanks in advance,
0
Gonzalo Becerra
Asked:
Gonzalo Becerra
  • 4
  • 3
  • 2
1 Solution
 
YZlatCommented:
$xmldata = [xml](Get-Content C:\MyFile.xml)
0
 
x-menIT super heroCommented:
$log = [XML]("<ROOT>"+(Get-Content .\solution.xml)+"</ROOT>")
$log.ROOT.Solutions.solution | % {"$($_.Name),$($_.LastOperationResult),$($_.LastOperationTime)"} >> out.csv
0
 
YZlatCommented:

$xmldata = [xml](Get-Content C:\Solutions.xml)

$nodelist = $xmldata.selectnodes("/Solutions/Solution")

foreach ($node in $nodelist)
{

      $solutionName=$node.getAttribute("Name")
      $lastOperationResult=$node.LastOperationResult

      $lastOperationTime=$node.LastOperationTime


}
0
 The Evil-ution of Network Security Threats

What are the hacks that forever changed the security industry? To answer that question, we created an exciting new eBook that takes you on a trip through hacking history. It explores the top hacks from the 80s to 2010s, why they mattered, and how the security industry responded.

 
Gonzalo BecerraAuthor Commented:
x-men Thanks for the script, it's work but in excel I see all in the same column. can we separate? without touching nothing inside the excel?.

YZlat: Thanks for the last script, but I cannot see the result.
0
 
x-menIT super heroCommented:

$objexcel = New-Object -com Excel.Application
$objexcel.Visible = $TRUE
$objexcel.DisplayAlerts = $FALSE
$workbook = $objexcel.workbooks.Add()
for ($i=$workbook.Sheets.count; $i -gt 1; $i--) {$workbook.Sheets.Item($i).delete()}

$intRow = 2
$workbook.Sheets.Item(1).Cells.Item(1, 1) = "Name"
$workbook.Sheets.Item(1).Cells.Item(1, 2) = "LastOperationResult"
$workbook.Sheets.Item(1).Cells.Item(1, 3) = "LastOperationTime"

$log = [XML]("<ROOT>"+(Get-Content .\solution.xml)+"</ROOT>")
$log.ROOT.Solutions.solution | % {"$($_.Name),$($_.LastOperationResult),$($_.LastOperationTime)"}

foreach ($solution in $log.ROOT.Solutions.solution)
      {
            $workbook.Sheets.Item(1).Cells.Item($intRow,1) = $solution.Name
            $workbook.Sheets.Item(1).Cells.Item($intRow,2) = $solution.LastOperationResult
            $workbook.Sheets.Item(1).Cells.Item($intRow,3) = $solution.LastOperationTime
            $intRow = $intRow +1
      }
$workbook.Sheets.Item(1).Cells.range("A1:C1").Font.Bold = $TRUE
$workbook.Sheets.Item(1).Cells.EntireColumn.AutoFit()
0
 
x-menIT super heroCommented:
If you get a error on line 3, change your regional settings to en-US, or run the code below:
function Use-Culture {  
    param([System.Globalization.CultureInfo][Parameter(Mandatory=$true)]$culture,  
            [ScriptBlock][Parameter(Mandatory=$true)]$code)  
    trap {[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture}  
    $currentCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture  
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture  
    Invoke-Command $code  
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture  
}
Use-Culture en-US {

$objexcel = New-Object -com Excel.Application
$objexcel.Visible = $TRUE
$objexcel.DisplayAlerts = $FALSE
$workbook = $objexcel.workbooks.Add()
for ($i=$workbook.Sheets.count; $i -gt 1; $i--) {$workbook.Sheets.Item($i).delete()}

$intRow = 2
$workbook.Sheets.Item(1).Cells.Item(1, 1) = "Name"
$workbook.Sheets.Item(1).Cells.Item(1, 2) = "LastOperationResult"
$workbook.Sheets.Item(1).Cells.Item(1, 3) = "LastOperationTime"

$log = [XML]("<ROOT>"+(Get-Content .\solution.xml)+"</ROOT>")
$log.ROOT.Solutions.solution | % {"$($_.Name),$($_.LastOperationResult),$($_.LastOperationTime)"}

foreach ($solution in $log.ROOT.Solutions.solution)
	{
		$workbook.Sheets.Item(1).Cells.Item($intRow,1) = $solution.Name
		$workbook.Sheets.Item(1).Cells.Item($intRow,2) = $solution.LastOperationResult
		$workbook.Sheets.Item(1).Cells.Item($intRow,3) = $solution.LastOperationTime
		$intRow = $intRow +1
	}
$workbook.Sheets.Item(1).Cells.range("A1:C1").Font.Bold = $TRUE
$workbook.Sheets.Item(1).Cells.EntireColumn.AutoFit()

}

Open in new window

0
 
Gonzalo BecerraAuthor Commented:
Wow excellent script thanks a lot, it's working.

My last question is how can we sort by LastOperationTime in the sheet? it's possible?
0
 
x-menIT super heroCommented:
here:
function Use-Culture {  
    param([System.Globalization.CultureInfo][Parameter(Mandatory=$true)]$culture,  
            [ScriptBlock][Parameter(Mandatory=$true)]$code)  
    trap {[System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture}  
    $currentCulture = [System.Threading.Thread]::CurrentThread.CurrentCulture  
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture  
    Invoke-Command $code  
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $currentCulture  
}
Use-Culture en-US {

$objexcel = New-Object -com Excel.Application
$objexcel.Visible = $TRUE
$objexcel.DisplayAlerts = $FALSE
$workbook = $objexcel.workbooks.Add()
for ($i=$workbook.Sheets.count; $i -gt 1; $i--) {$workbook.Sheets.Item($i).delete()}

$intRow = 2
$workbook.Sheets.Item(1).Cells.Item(1, 1) = "Name"
$workbook.Sheets.Item(1).Cells.Item(1, 2) = "LastOperationResult"
$workbook.Sheets.Item(1).Cells.Item(1, 3) = "LastOperationTime"

$log = [XML]("<ROOT>"+(Get-Content .\solution.xml)+"</ROOT>")

$temp = @()
foreach ($solution in $log.ROOT.Solutions.solution)
	{$aux = "" | Select-Object Name,LastOperationResult,LastOperationTime
		$aux.Name = $solution.Name
		$aux.LastOperationResult = $solution.LastOperationResult
		$aux.LastOperationTime = $solution.LastOperationTime
		$temp += $aux
}
$temp = $temp | Sort-Object LastOperationTime -Descending
foreach ($solution in $temp) 
{		$workbook.Sheets.Item(1).Cells.Item($intRow,1) = $solution.Name
		$workbook.Sheets.Item(1).Cells.Item($intRow,2) = $solution.LastOperationResult
		$workbook.Sheets.Item(1).Cells.Item($intRow,3) = $solution.LastOperationTime
		$intRow = $intRow +1
	}
$workbook.Sheets.Item(1).Cells.range("A1:C1").Font.Bold = $TRUE
$workbook.Sheets.Item(1).Cells.EntireColumn.AutoFit()

}

Open in new window

0
 
Gonzalo BecerraAuthor Commented:
Thanks a lot for your help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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