Solved

Read data from XML

Posted on 2011-09-26
9
323 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
ID: 36599314
$xmldata = [xml](Get-Content C:\MyFile.xml)
0
 
LVL 18

Expert Comment

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

Expert Comment

by:YZlat
ID: 36599404

$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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 1

Author Comment

by:Gonzalo Becerra
ID: 36599487
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
 
LVL 18

Expert Comment

by:x-men
ID: 36599656

$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
ID: 36599671
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
ID: 36599718
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
ID: 36600054
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
ID: 36600514
Thanks a lot for your help!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

813 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

17 Experts available now in Live!

Get 1:1 Help Now