Generate XML with attributes using PowerShell

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.

Anxious Daffy: don't despair! — The key to most solutions:
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.