如何使用PowerShell 2.0将XML转换为CSV ?

I'm trying to convert the following XML to CSV using PowerShell 2.0, I can get some of the data out but when trying to go through the XML objects I can't seem to get just the data in a format that would allow me to make a good table.

我正在尝试使用PowerShell 2.0将下面的XML转换为CSV，我可以得到一些数据，但是当我尝试遍历XML对象时，我似乎无法得到格式良好的数据。

What I have is something like this, with multiple item's

我有的东西是这样的，有多个项目

<root type="array">   
  <item type="object">
    <short_version type="string">11</short_version>
    <long_name type="string">Internet Explorer</long_name>
    <api_name type="string">internet explorer</api_name>
    <long_version type="string">11.0.9600.16384.</long_version>
    <latest_stable_version type="string"></latest_stable_version>
    <automation_backend type="string">webdriver</automation_backend>
    <os type="string">Windows 2012 R2</os>
  </item>
  ... 
</root>

<根类型=“数组”>

Either I end up with the Type or if I try and access the InnerHTML I get only the values, but in a long string.

要么得到类型，要么尝试访问InnerHTML，我只能得到值，但是是一个长字符串。

I'm have so far:

我迄今为止:

[xml]$convertMe = Get-Content $jsonTemp
$convertMe.SelectNodes("//item") | % { $_.InnerText }

How can I get this in a nice CSV format like:

我怎样才能得到一个很好的CSV格式:

short_version,long_name,api_name,long_version,latest_stable_version,automation_backend,os 11,Internet Explorer,internet explorer,11.0.9600.16384,,webdriver,Windows 2012 R2

short_version,long_name,api_name,long_version,latest_stable_version, automation_后端，os 11,Internet Explorer, Internet Explorer, 11.0.9600.16384, webdriver,Windows 2012 R2

3 个解决方案

#1

This is a hard-coded and long solution, but it works. :) Try:

这是一个硬编码和长的解决方案，但是它是有效的。:试一试:

$xml = [xml](Get-Content .\test.xml)

$xml.root.item | Select-Object @(
@{l="short_version";e={$_.short_version."#text"}},
@{l="long_name";e={$_.long_name."#text"}},
@{l="api_name";e={$_.api_name."#text"}},
@{l="long_version";e={$_.long_version."#text"}},
@{l="latest_stable_version";e={$_.latest_stable_version."#text"}},
@{l="automation_backend";e={$_.automation_backend."#text"}},
@{l="os";e={$_.os."#text"}}) |
Export-Csv test.csv -NoTypeInformation

test.csv

"short_version","long_name","api_name","long_version","latest_stable_version","automation_backend","os"
"11","Internet Explorer","internet explorer","11.0.9600.16384.",,"webdriver","Windows 2012 R2"

And alternative and probably slower solution:

还有另一种可能更慢的解决方案:

$xml = [xml](Get-Content .\test.xml)

#Foreach item
$xml.root.item | ForEach-Object {
    $obj = New-Object psobject
    $_.GetEnumerator() | ForEach-Object {
        #Get all properties/elements and values
        $obj | Add-Member -MemberType NoteProperty -Name $_.Name -Value $_.InnerText
    }
    $obj
} |
#Set property order. This also makes sure that all items exports the same properties(which a csv needs)
Select-Object short_version,long_name,api_name,long_version,latest_stable_version,automation_backend,os |
#Export to csv
Export-Csv test.csv -NoTypeInformation

#2

It may be a little messy but it gets you exactly what you're looking for.

它可能有点乱，但它能让你找到你想要的。

[xml]$convertMe = Get-Content $jsonTemp
[Array]$MeConverted = $convertMe.GetElementsByTagName('item')
$Collection = @()
ForEach($Record in $MeConverted){
    $Output = new-object psobject
    $Record.selectnodes("*")|%{Add-Member -InputObject $Output -MemberType NoteProperty -Name $_.Name -Value $_.'#text'}
    If($Collection){
        $T2Keys = $Collection|gm|?{$_.MemberType -match "Property"}|Select -ExpandProperty Name
        $T1Keys = $Output|gm|?{$_.MemberType -match "Property"}|Select -ExpandProperty Name
        $KeysToAdd = $T2Keys|?{$T1Keys -notcontains $_}
        $KeysToAdd|%{$Collection|Add-Member $_ ""}
    }
    $Collection += $Output
}
$Collection | Export-CSV file.csv -notype

Edit: Frode's looks a lot cleaner in my opinion, but mine does have the advantage of not having to know all of the child nodes' names.

编辑:在我看来，Frode看起来要干净得多，但我的确有优势，不需要知道所有的子节点的名称。

Edit2: Fixed glaring flaws, obviously there will be more than one item and I totally didn't account for that. That just made my code a lot bigger because I need to account for additional potential properties. Now for each item it checks for additional properties that weren't in the collection before and adds them before adding that record to the collection.

修正了明显的缺陷，很明显会有不止一个项目，我完全没有解释。这使得我的代码变得更大了，因为我需要考虑其他的潜在属性。现在，对于每个项目，它检查之前没有在集合中的其他属性，并在将记录添加到集合之前添加它们。

Edit3: Updated the Add-Member command for backwards compatibility. I wasn't aware that -NotePropertyName/Value are v3+ until now.

Edit3:为向后兼容性更新Add-Member命令。直到现在我才知道-NotePropertyName/Value是v3+。

#3

I threw together a little PowerShell function that does this. It assumes a lot about your XML structure, but it worked with the content you posted. You'll just have to pipe it to an Out-File to get your CSV.

我加了一个PowerShell函数。它假定您的XML结构非常复杂，但是它与您所发布的内容一起工作。你只需要将它传输到外文件就可以获得CSV。

Function ConvertFrom-XMLtoCSV {
    [CmdletBinding()]
    <#
    .Synopsis
       Convert a uniform XML file to CSV with element names as headers
    .DESCRIPTION
       Takes a uniformed XML tree and converts it to CSV based on the XPath given.

       For example, assume a structure like this:
       <root>
           <item>
               <element1>Content1</element1>
               <element2>Content2</element2>
           </item>
           <item>
               <element1>Content1</element1>
               <element2>Content2</element2>
           </item>
       <root>

    .PARAMETER Path
        The path to the XML File

    .PARAMETER XPath
        The XPath query to the items that should be converted

    .EXAMPLE
       ConvertFrom-XMLtoCSV -Path .\file.xml -XPath "//item" 
    #>
    Param (
        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,Position=0)][String] $Path,
        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,Position=1)][String] $XPath
    )
    Begin {
        if (Test-Path $Path) {
            [XML] $XML = Get-Content $Path -Raw
        } else {
            Throw [System.IO.FileNotFoundException] "XML file was not found at the given path"
        }
        $NodeCount = $XML.SelectNodes($XPath).Count
        $FileHeaders = [System.String]::Join(",",$($XML.SelectNodes("$XPath[1]/node()") | ForEach-Object { $_.ToString()}))
        $Content = @()
    }
    Process {
        $Content += $FileHeaders
        For ($i = 1; $i -le $NodeCount; $i++) { 
            $Content += [System.String]::Join(",",$($xml.SelectNodes("$XPath[$i]/node()") | ForEach-Object {$_."#text"}))
        }

        return $Content
    }
}

#1