Today I discovered that generating XML with PowerShell isn't as straight forward as I had hoped. I started off with a CSV that I had to convert to XML. Fortunately PowerShell is very good when it comes to CSV. It was the parsing of the object to XML-attributes that proved the most challenging part.
The CSV
I've got a big list of entities with a start and an end date. I have to convert all the objects that are in range of today to XML. The list looks like this:
Code;Name;Start Date;End Date;SKA;ZKA
K58;LEGO;2011-01-12 00:00:00.000;2120-10-31 00:00:00.000;1;0
LAD;LEGO Angry Birds;2016-12-22 00:00:00.000;2118-12-22 00:00:00.000;0;0
LAR;LEGO Architecture;2016-12-22 00:00:00.000;2118-12-22 00:00:00.000;0;0
LMO;LEGO Batman Movie;2014-04-24 00:00:00.000;2034-04-24 00:00:00.000;0;0
LBI;LEGO Bionicle;2020-12-22 00:00:00.000;2118-12-22 00:00:00.000;0;0
LCI;LEGO City;2014-07-07 00:00:00.000;2034-07-07 00:00:00.000;0;0
LEC;LEGO Classic;2016-12-22 00:00:00.000;2120-12-22 00:00:00.000;0;0
LCR;LEGO Creator;2014-07-07 00:00:00.000;2034-07-07 00:00:00.000;0;0
E25;Lego Dimensions;2020-03-09 00:00:00.000;2120-03-09 00:00:00.000;0;0
LDI;LEGO Disney;2014-04-24 00:00:00.000;2030-04-24 00:00:00.000;0;0
KIH;LEGO Duplo;2012-06-08 00:00:00.000;2120-01-01 00:00:00.000;0;0
EV1;LEGO Elves;2015-12-14 00:00:00.000;2118-12-14 00:00:00.000;0;0
LJW;LEGO Jurassic World;2018-05-14 00:00:00.000;2120-05-14 00:00:00.000;0;0
LFR;LEGO Friends;2013-06-24 00:00:00.000;2016-01-23 00:00:00.000;0;0
Desired output
I need to convert the data into the following XML:
<?xml version="1.0" ?>
<entities>
<entity code="K58" name="LEGO" />
<entity code="LAD" name="LEGO Angry Birds" />
<entity code="LAR" name="LEGO Architecture" />
<entity code="LMO" name="LEGO Batman Movie" />
<entity code="LCI" name="LEGO City" />
<entity code="LEC" name="LEGO Classic" />
<entity code="LCR" name="LEGO Creator" />
<entity code="LDI" name="LEGO Disney" />
<entity code="KIH" name="LEGO Duplo" />
<entity code="EV1" name="LEGO Elves" />
<entity code="LJW" name="LEGO Jurassic World" />
</entities>
CSV to objects – the easy part
Converting and filtering the CSV is not so hard:
Import-Csv .\entities.csv -delimiter ";" |
Where-Object {
$_."Start Date" -as [DateTime] -le $(Get-Date) -and
$_."End Date" -as [DateTime] -ge $(Get-Date)
} |
Select-Object -Property Code, Name
The output looks like this:
Code Name
---- ----
K58 LEGO
LAD LEGO Angry Birds
LAR LEGO Architecture
LMO LEGO Batman Movie
LCI LEGO City
LEC LEGO Classic
LCR LEGO Creator
LDI LEGO Disney
KIH LEGO Duplo
EV1 LEGO Elves
LJW LEGO Jurassic World
Writing to XML
So there are a view ways to generate XML in PowerShell. Let's explore.
ConvertTo-Xml -NoTypeInformation?
I had hoped that this would help me, but it didn't. The output looks like this:
<?xml version="1.0" encoding="utf-8"?>
<Objects>
<Object>
<Property Name="Code">K58</Property>
<Property Name="Name">LEGO</Property>
</Object>
<Object>
<Property Name="Code">LAD</Property>
<Property Name="Name">LEGO Angry Birds</Property>
</Object>
Export-Clixml?
This one is not going to work for me either:
<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
<Obj RefId="0">
<TN RefId="0">
<T>Selected.System.Management.Automation.PSCustomObject</T>
<T>System.Management.Automation.PSCustomObject</T>
<T>System.Object</T>
</TN>
<MS>
<S N="Code">K58</S>
<S N="Name">LEGO</S>
</MS>
</Obj>
Use .NET to write XML?
I found a great blog by Jeff Hicks on how to use .NET in PowerShell to write an XML file. One of the drawbacks of .NET is that it is really verbose; you'll need to create a document variable, you need to create a root node, entity nodes and attributes and add them together. It felt like a lot of hassle for something that should be simple and straight-forward.

don't become desperate -
easier said then done!
Using strings?
So... I ended up with string concatenation. It was not where I hoped to end up, but it turns out it was the easiest way of achieving my goal: an XML file with attributes:
$data = (
Import-Csv .\entities.csv -delimiter ";" |
Where-Object {
$_."Start Date" -as [DateTime] -le $(Get-Date) -and
$_."End Date" -as [DateTime] -ge $(Get-Date)
} |
Select-Object -Property Code, Name |
ForEach-Object {
"`t" +
'<entity code="' +
[System.Security.SecurityElement]::Escape($_.Code) +
'" name="' +
[System.Security.SecurityElement]::Escape($_.Name) +
'" />'
}) -join "`n"
@"
<?xml version="1.0" ?>
<entities>
$data
</entities>
"@ | Out-File "results.xml"
We first generate a list of strings - based on each object. We join those lines together and than we wrap that data with the XML specification and a root node.
There are some things going on that took me some Googling:
- `t and `n were can be used to generate tabs and enters into string literals - but this will only work if you use double quotes.
- The System.Security.SecurityElement.Escape will escape XML characters. Yeah, a little .NET comes a long way.
- The @""@ is a here-string that will preserve the line breaks.
Summary
To sum up: don't fear string concatenation, remember to escape XML characters and you'll be fine. It might not be the solutions you wanted... but at least it is a solution that works.
