After my last post on parsing the XML, I decided to continue forward and get ready to put the data in a database. For that, I’m really looking for this data:

• event ID
• session title

With this, I can easily insert data into a table. I’ll have separate tables for the events themselves and the speakers, but for now, I can easily showcase the titles of the sessions.

With that in mind, I decided to start expanding my efforts and building a series of loops that get all the data from the XML documents.

Looping through all files

The first thing I needed to do was loop through all the files I’d downloaded and get the documents loaded. I decided to use a DO loop for this, since I should be doing this at least once each time. Eventually I’ll add logic to avoid downloading files I’ve downloaded already.

Here’s the basic code:

$loop = 1

$loopend = 450
$doc = New-Object System.Xml.XmlDocument

do {
#start large loop

  # get the filename
  $sourceURL = $baseURL + $i + ".xml"

  # do other stuff

  $i++

#end outer loop
} while ($i -lt $loopend)

This is the basis for looping through all the file names, based on my downloads. A quick test shows this is building all the filenames I need.

Loading files

The next step is to actually load each XML file in and start querying it. I changed from the parsing code to use a loop since I’ll need to insert each item separately and I don’t think the code I had from the previous article will work. At least, I haven’t found a way.

If you know of one, let me know.

I used the Test-Path method to be sure that the XML exists, as there was at least one lost event in my initial download. I think that’s fixed now, but in any case, I added this code:

#test the path first. If it exists, load the XML
if (Test-Path $sourceURL) {
  $doc.Load($sourceURL

#trap the event number. This will be the ID I use in the database table.
    $event = "SQL Saturday #" + $i

That seems to work fine, and with with $event variable, I know which event the sessions are associated with.

Next Steps

That’s all I wanted to put here, giving me a nice, simple way of going through a series of files in a pattern. From here I’ll add more detail to the inner loop that gets the session titles out of the XML document and displays it.