Solved

Read data from XML

Posted on 2011-09-26
9
319 Views
Last Modified: 2012-05-12
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
Comment
Question by:Gonzalo Becerra
  • 4
  • 3
  • 2
9 Comments
 
LVL 35

Expert Comment

by:YZlat
Comment Utility
$xmldata = [xml](Get-Content C:\MyFile.xml)
0
 
LVL 18

Expert Comment

by:x-men
Comment Utility
$log = [XML]("<ROOT>"+(Get-Content .\solution.xml)+"</ROOT>")
$log.ROOT.Solutions.solution | % {"$($_.Name),$($_.LastOperationResult),$($_.LastOperationTime)"} >> out.csv
0
 
LVL 35

Expert Comment

by:YZlat
Comment Utility

$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
 
LVL 1

Author Comment

by:Gonzalo Becerra
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 18

Expert Comment

by:x-men
Comment Utility

$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
 
LVL 18

Expert Comment

by:x-men
Comment Utility
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
 
LVL 1

Author Comment

by:Gonzalo Becerra
Comment Utility
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
 
LVL 18

Accepted Solution

by:
x-men earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:Gonzalo Becerra
Comment Utility
Thanks a lot for your help!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now