Powershell parse xml values into csv

Question

I have an xml file that contains two different values that I am trying to capture in each line. I am trying to use Powershell to parse them into a csv, two columns, one being Device ID and one being MAC address, with headings. Any suggestions would be greatly appreciated. Below is a sample of what the xml file looks like that I would be working with. Thank you for your time.

<?xml version="1.0" encoding="utf-8"?>
<DeviceConfig>
    <Device RegisterID="1021" MacID="A4F1E85D7D86" />
    <Device RegisterID="1022" MacID="A4F1E85D056E" />
    <Device RegisterID="1023" MacID="A4F1E85CAAEE" />
    <Device RegisterID="1024" MacID="A4F1E85DB284" />
    <Device RegisterID="1025" MacID="A4F1E85D7021" />
    <Device RegisterID="1026" MacID="A4F1E85D034A" />
    <Device RegisterID="1027" MacID="A4F1E85CAD59" />
    <Device RegisterID="1028" MacID="A4F1E85DAFA2" />
    <Device RegisterID="1029" MacID="A4F1E85D050E" />
    <Device RegisterID="1030" MacID="A4F1E85DAF89" />
    <Device RegisterID="1031" MacID="A4F1E85DA80E" />
</DeviceConfig>

Kory Gill · Accepted Answer · 2016-09-23 23:46:30Z

4

You can replace my here string with a call to Get-Content for your actual file, but this works for me, and does not need to bring in .NET classes.

Code

$xml = @"
<?xml version="1.0" encoding="utf-8"?>
<DeviceConfig>
    <Device RegisterID="1021" MacID="A4F1E85D7D86" />
    <Device RegisterID="1022" MacID="A4F1E85D056E" />
    <Device RegisterID="1023" MacID="A4F1E85CAAEE" />
    <Device RegisterID="1024" MacID="A4F1E85DB284" />
    <Device RegisterID="1025" MacID="A4F1E85D7021" />
    <Device RegisterID="1026" MacID="A4F1E85D034A" />
    <Device RegisterID="1027" MacID="A4F1E85CAD59" />
    <Device RegisterID="1028" MacID="A4F1E85DAFA2" />
    <Device RegisterID="1029" MacID="A4F1E85D050E" />
    <Device RegisterID="1030" MacID="A4F1E85DAF89" />
    <Device RegisterID="1031" MacID="A4F1E85DA80E" />
</DeviceConfig>
"@

$xmlData = [xml]$xml

$xmlData.DeviceConfig.ChildNodes | ConvertTo-Csv -NoTypeInformation

Output

"RegisterID","MacID"
"1021","A4F1E85D7D86"
"1022","A4F1E85D056E"
"1023","A4F1E85CAAEE"
"1024","A4F1E85DB284"
"1025","A4F1E85D7021"
"1026","A4F1E85D034A"
"1027","A4F1E85CAD59"
"1028","A4F1E85DAFA2"
"1029","A4F1E85D050E"
"1030","A4F1E85DAF89"
"1031","A4F1E85DA80E"

With this information, you should be able to make progress...

answered Sep 23, 2016 at 23:46

Kory Gill

7,1711 gold badge28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jason Boyd Over a year ago

I like this approach too.

LilithGoddess Over a year ago

This worked perfectly - thank you so much for the assistance! By using Export-csv instead of ConvertTo-csv, I was able to export to a .csv file like I wanted. Cheers!

LilithGoddess Over a year ago

There is one additional piece of info I would like to add to the beginning of each row in the .csv file. I have a variable for $servername in my larger script, and I would like to put a column for "Server Name" in this .csv and add the value of $servername in each row (before the RegisterID and MacID). Any ideas on how to best approach this? Thank you!

LilithGoddess Over a year ago

If I should start a new thread for this, just let me know. :)

Kory Gill Over a year ago

A new question would be best and in the spirit of SO.

Jason Boyd · Accepted Answer · 2016-09-23 23:43:22Z

2

I think something like this will work. Here I am reading the XML from a string, you may be importing it from a file:

$xml = @"
<?xml version="1.0" encoding="utf-8"?>
<DeviceConfig>
    <Device RegisterID="1021" MacID="A4F1E85D7D86" />
    <Device RegisterID="1022" MacID="A4F1E85D056E" />
    <Device RegisterID="1023" MacID="A4F1E85CAAEE" />
    <Device RegisterID="1024" MacID="A4F1E85DB284" />
    <Device RegisterID="1025" MacID="A4F1E85D7021" />
    <Device RegisterID="1026" MacID="A4F1E85D034A" />
    <Device RegisterID="1027" MacID="A4F1E85CAD59" />
    <Device RegisterID="1028" MacID="A4F1E85DAFA2" />
    <Device RegisterID="1029" MacID="A4F1E85D050E" />
    <Device RegisterID="1030" MacID="A4F1E85DAF89" />
    <Device RegisterID="1031" MacID="A4F1E85DA80E" />
</DeviceConfig>
"@

$csv = `
    Select-Xml -Content $xml -XPath "//Device" `
    | Select -ExpandProperty Node `
    | ConvertTo-Csv -NoTypeInformation

$csv

This is the content of $csv:

"RegisterID","MacID"
"1021","A4F1E85D7D86"
"1022","A4F1E85D056E"
"1023","A4F1E85CAAEE"
"1024","A4F1E85DB284"
"1025","A4F1E85D7021"
"1026","A4F1E85D034A"
"1027","A4F1E85CAD59"
"1028","A4F1E85DAFA2"
"1029","A4F1E85D050E"
"1030","A4F1E85DAF89"
"1031","A4F1E85DA80E"

You should be able to write that out to a file if you need.

answered Sep 23, 2016 at 23:43

Jason Boyd

7,0495 gold badges33 silver badges53 bronze badges

1 Comment

Kory Gill Over a year ago

This is a good approach. If the xml is simple and/or you don't need to build queries, I like the '[xml]' type casting.

Mike Makarov · Accepted Answer · 2016-09-23 23:38:56Z

0

You need to use .Net classes here, pure PS won't help. Fortunately, it's easy.

Add-Type -AssemblyName System.Xml.Linq
$xe = [System.Xml.Linq.XElement]::Parse($xml)
$xe.Elements() | % {$_.Attribute('RegisterID').Value + ", " + $_.Attribute('MacID').Value}

This prints what you need to output. Saving to file is covered by many other replies.

answered Sep 23, 2016 at 23:38

Mike Makarov

1,3661 gold badge9 silver badges19 bronze badges

1 Comment

LilithGoddess Over a year ago

I actually found that it was not necessary to use .Net classes. Thank you for your help!

Collectives™ on Stack Overflow

Powershell parse xml values into csv

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related