Blog

Ever had a folder with a bunch of XML documents that you needed to query? Read on to see how you can extract values from those XML documents using PowerShell.

Where I currently work, one of my assignment was to automatically generate XML-files containing international outgoing payments (in the SEPA format). Each file had a message id (MsgId) to make the file unique. The MsgId was simply an integer that started at 1 and incremented by 1 for each new file.

After a year or two, for some reason errors suddenly started appearing in the file so the bank rejected the file. The errors were easily corrected. But the problem was that the MsgId was consumed and could not be used again. My solution to this was to replace the MsgId with some very high integer, so that it won’t interfere with the incrementing values in the future. For instance 999999. Problem was, I had used this technique before but what was the previous value?

So I needed to scan all previous XML files to see what the highest MsgId value was. I realize there are lots of different ways to solve this, but the solution I opted for was to use PowerShell.

The “top” of the XML looked like this:

<?xml version="1.0" encoding="utf-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.001.001.03">
  <CstmrCdtTrfInitn>
    <GrpHdr>
      <MsgId>1234</MsgId>

So. From a collection of hundreds of such documents I needed to know the highest MsgId. The PowerShell script that gave me that is the following:

$files = Get-ChildItem C:\Temp\SEPA\*.*

foreach ($file in $files)
{
    [xml][/xml]$doc = Get-Content –Path $file.FullName
    $curr = $doc.Document.CstmrCdtTrfInitn.GrpHdr.MsgId
    $max = if ($curr -gt $max) {$curr} else {$max}
    #$max
}

$max

The first row simply indicates where the files are. The foreach loop iterates through each file in the catalog. The Get-Content statement loads the XML file into memory. The next row navigates in the XML hierarchy to the correct node (Document.CstmrCdtTrfInitn.GrpHdr.MsgId). The if statement loads the current document value in the variable $max if it is bigger. Finally, the last row only outputs the result.

Simple, right 🙂

This just serves as an example of how to extract values from XML files. I think there might be many more use cases for this technique.

Also check out my blog post where I demonstrated how to get values from PowerShell into SQL Server.