Importing XML into a RecordSet

Published 02/12/02 - For Classic ASP

Introduction

I find navigating XML using the XMLDOM object to be hell. Some people seem to get the hang of it, but I don't. However, I find navigating a RecordSet to be much easier. This article will detail how to import an XML file into a RecordSet and to navigate it.

Connection

A lot of people will recognize this code -

Dim objRS Set objRS = Server.CreateObject("ADODB.RecordSet") objRS.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;" objRS.Open(Server.MapPath("news.xml"))

This code will create the recordset that we will store the XML in. It is simply a regular OleDb connection using the MSDAOSP provider.

For more information on news.xml see here.

Looking at the RecordSet

You can navigate this recordset like any other -

Do While Not objRS.EOF Response.Write(objRS.Fields(0) & "<br>") objRS.MoveNext Loop

Live Demo

This code will only print out the <Headline> Tag. This is because the recordset is organized like this -

<table border="1" width="100%"> <% Response.Write("<tr>") For Each fld in objRS.Fields If fld.Name <> "$Text" Then Response.Write("<td>" & fld.Name & "</td>") End If Next Response.Write("</tr>") Do While Not objRS.EOF Response.Write("<tr>") For Each fld in objRS.Fields If fld.Name <> "$Text" Then Response.Write("<td>" & fld.Value & "</td>") End If Next Response.Write("</tr>") objRS.MoveNext Loop %> </table>

Live Demo

We ignore the $Text column because it is the entire record in one field so it doesn't look very nice.

Now you can navigate the XML file as if it was a regular recordset.

Summary

With this solution, you'll find it much easier to navigate XML files that can be tedious task using the XMLDOM.