Parsing XML Child Nodes and Converting to DateTime with PowerShell

As part of my organiser role for SQLSaturday Exeter (Training Day Information here and Saturday Information here) I needed to get some schedule information to input into a database.

I had read Steve Jones blog posts on Downloading SQL Saturday Data and followed the steps there to download the data from the SQL Saturday website for our event.

A typical session is held in the XML like this

 

I needed to output the following details – Speaker Name , Room , Start time,Duration and Title

To accomplish this I examined the node for Williams session

I then established that to get the speakers name I had to obtain the value from the child node which I accomplished as follows

This is an easy way to obtain sub(or child) properties within a select in PowerShell and I would recommend that you practice and understand that syntax of @{Name=””; Expression = {} } which will enable you to perform all kinds of manipulation on those objects. You are not just limited to obtaining child properties but can perform calculations as well

I did the same thing to get the room and the start time

I then needed duration and thought that I could use

However that just gave me a blank result so to troubleshoot I ran

Which errored with the (obvious when I thought about it) message

Cannot convert value “4/25/2015 4:10:00 PM” to type “System.Int32”. Error: “Input string was not in a correct format.”
At line:1 char:1
+ $Sessions.event[39].endtime – $sessions.event[39].startTime
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvalidCastFromStringToInteger

The value was stored as a string

Running

showed me that there was a method called ToDateTime but there is an easier way. By defining the datatype of an object Powershell will convert it for you so the resulting code looks like this

and the resulting entry is finally as I required it. I believe that this will use the regional settings from the installation on the machine that you are using but I have not verified that. If anyone in a different region would like to run this code and check that that is the case I will update the post accordingly

zzCapture

Hopefully you have learnt from this how you can extend select from the pipeline and how defining the datatype can be beneficial. Any questions please comment below

3 thoughts on “Parsing XML Child Nodes and Converting to DateTime with PowerShell

  1. Pingback: BPOTW 2015-03-27 | SQL Notes From The Underground

  2. Thats some cool stuff.
    I would like to start using powershell with my sql developer functions…. this might be a good start.
    I was wondering if you could do this with a web services xml file output?
    I am currently getting an XML file but would like to automate it and pull the data directly into our database.
    Is that possible and can you give a newbie to SQL 2012 and XML some beginner tips?

    Thanks.

  3. Pingback: PowerShelling SQL Saturday Sessions to the Guidebook app « SQL DBA with A Beard

Please feel free to comment on this post. All comments are moderated first before appearing on the site