Parsing SQL Saturday Data – Looping Through And Loading All XML Files

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.