Gonzalo Becerra
asked on
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
Thanks in advance,
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>
Thanks in advance,
$xmldata = [xml](Get-Content C:\MyFile.xml)
$log = [XML]("<ROOT>"+(Get-Conten t .\solution.xml)+"</ROOT>")
$log.ROOT.Solutions.soluti on | % {"$($_.Name),$($_.LastOper ationResul t),$($_.La stOperatio nTime)"} >> out.csv
$log.ROOT.Solutions.soluti
$xmldata = [xml](Get-Content C:\Solutions.xml)
$nodelist = $xmldata.selectnodes("/Sol
foreach ($node in $nodelist)
{
$solutionName=$node.getAtt
$lastOperationResult=$node
$lastOperationTime=$node.L
}
ASKER
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.
YZlat: Thanks for the last script, but I cannot see the result.
$objexcel = New-Object -com Excel.Application
$objexcel.Visible = $TRUE
$objexcel.DisplayAlerts = $FALSE
$workbook = $objexcel.workbooks.Add()
for ($i=$workbook.Sheets.count
$intRow = 2
$workbook.Sheets.Item(1).C
$workbook.Sheets.Item(1).C
$workbook.Sheets.Item(1).C
$log = [XML]("<ROOT>"+(Get-Conten
$log.ROOT.Solutions.soluti
foreach ($solution in $log.ROOT.Solutions.soluti
{
$workbook.Sheets.Item(1).C
$workbook.Sheets.Item(1).C
$workbook.Sheets.Item(1).C
$intRow = $intRow +1
}
$workbook.Sheets.Item(1).C
$workbook.Sheets.Item(1).C
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()
}
ASKER
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?
My last question is how can we sort by LastOperationTime in the sheet? it's possible?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot for your help!