VBScript Scripting Techniques > Files > XML
VBScript Scripting Techniques > Databases > XML

XML

To test the code on this page yourself will require some preparation.

I will be using an XML file created by exporting data from DVD Profiler. So you may want to download the free version first, and start creating your own database.
As an alternative, you can copy and paste this single record from my own DVD database, but it doesn't demonstrate the queries quite as good as a "real" populated database does.

Next you may want to download and install Microsoft's free XML Notepad 2007.
It can be used to display the XML file in tree view and XSL view, which makes it easier to see and understand the queries we are going to build.
Or as alternatives, download XMLFox, or use your browser, or Notepad, or any other text or programmer's editor (or for the diehards: TYPE and MORE).

Let's get started.
In your editor, IDE or whatever, type the following code:

Set xmlDoc = CreateObject( "Microsoft.XMLDOM" )
xmlDoc.Async = "False"
xmlDoc.Load( "dvdcoll.xml" )

If you have an integrated object browser, you'll probably see a reference to MSXML being inserted.

We'll start with simple query, display each DVD's title:

strQuery = "/Collection/DVD/Title"
Set colNodes = xmlDoc.selectNodes( strQuery )
For Each objNode in colNodes
	WScript.Echo objNode.nodeName & ": " & objNode.text
Next

Save the code as a VBScript file and run it.
The result will look like this:

Title: Wild Down Under
Title: Pride
Title: Gandhi
Title: The Abyss
Title: Shouf Shouf Habibi

So, why was the query built like this?

Take a look at the image on the right, a fragment of a screenshot of XML Notepad's "XSL Output" window with our XML file loaded (or open the XML file in a plain text editor).

What we see is an opening tag <Collection> as the first tag. This translates to /Collection in the query.
Then, between the opening <Collection> and closing </Collection> tags ("inside" the Collection), note the "recordsets" of <DVD> and </DVD> tags. These translate to the DVD part of the query: in the root we find Collections, and inside (or under) Collections we find DVD — just like a directory structure on a disk.
Inside (or under) DVD there are several tags, Title being one of them. So now we have /Collection/DVD/Title.

Now let's move to the next level of complexity for our queries, display multiple properties:

strQuery = "/Collection/DVD/ ( Title | Genres )"

The part of the query between parentheses is a list of choices, separated by "pipe" characters (|).
Tags will be listed if they match any of these choices, so Title as well as Genres will be shown for each DVD in the collection.

The result will look like this:

Title: Wild Down Under
Genres: Documentary Special Interest
Title: Pride
Genres: Documentary
Title: Gandhi
Genres: Drama Classic
Title: The Abyss
Genres: Science-Fiction Suspense/Thriller
Title: Shouf Shouf Habibi
Genres: Comedy

The genres are listed as single lines, where each line can contain one (or zero) or more genres.

If you look further down in the XML structure, you'll notice that some keys or tags have subkeys: Genres for example uses Genre subkeys.
Subkeys are used when multiple values are possible. They can be viewed best in XML Notepad's treeview.
To list each individual genre we must use /Collection/DVD/Genres/Genre.

So next, let's display the Title and each individual Genre:

strQuery = "/Collection/DVD/ ( Title | Genres/Genre )"

Roughly translated: display anything that matches "/Collection/DVD/Title" or "/Collection/DVD/Genres/Genre"

The result will look like this:

Title: Wild Down Under
Genre: Documentary
Genre: Special Interest
Title: Pride
Genre: Documentary
Title: Gandhi
Genre: Drama
Genre: Classic
Title: The Abyss
Genre: Science-Fiction
Genre: Suspense/Thriller
Title: Shouf Shouf Habibi
Genre: Comedy

Note how some DVDs have multiple Genres.

OK, listing properties for each item in a collection no longer has any secrets for you.
Let's select specific items (DVDs) from the collection.
List the titles and genres of all documentaries:

strQuery = "/Collection/DVD [ Genres/Genre = 'Documentary' ] / ( Title | Genres/Genre )"

The result will look like this:

Title: Wild Down Under
Genre: Documentary
Genre: Special Interest
Title: Pride
Genre: Documentary
Title: March of the Penguins
Genre: Documentary
Title: Alaska: Spirit of the Wild
Genre: Documentary
Genre: Special Interest
Title: Wilderness Journey - Canyon Suites
Genre: Documentary
Genre: Special Interest
Genre: Music

It is possible to select based on genre without displaying it:

strQuery = "/Collection/DVD [ Genres/Genre = 'Documentary' ] /Title"

would only display the titles of the documentaries.

Finally, the selection can be narrowed down by using and and or. Note that these are case sensitive, so And will not work.

List titles and all formats for each documentary that supports 16x9 format:

strQuery = "/Collection/DVD " _
         & "[ Genres/Genre = 'Documentary' and Format/Format16X9 = 'True' ] " _
         & "/ ( Title | Format/* )"

The result will look like this:

Title: Wild Down Under
FormatAspectRatio: 1.78
FormatVideoStandard: PAL
FormatLetterBox: True
FormatPanAndScan: False
FormatFullFrame: False
Format16X9: True
FormatDualSided: False
FormatDualLayered: False
FormatFlipper: False
Title: Pride
FormatVideoStandard: PAL
FormatLetterBox: True
FormatPanAndScan: False
FormatFullFrame: True
Format16X9: True
FormatDualSided: False
FormatDualLayered: True
FormatFlipper: False

. . . TO BE CONTINUED . . .

[Back to the top of this page]

Related Stuff:

• XML DOM Properties
• XML DOM Methods
• Save information to an XML file, by Adriaan Westra
• Get information from an XML file, by Adriaan Westra