How To Retrieve XML Data in ASP with the XML OLE-DB Simple Provider


This article includes step-by-step instructions to set up an ASP client that demonstrates the use of the OLEDB Simple Provider for XML to access hierarchical XML data.
The OLE-DB Simple Provider for XML (XML OSP) can be used to load data in an XML document into a read-only ADO recordset. The data is then read and accessed by using the standard methods of the ADO Recordset object. This provider can be used to provide a different method of working with hierarchical data that is contained in XML documents.

More Information

Run the following steps in sequence to set up and test an ASP page that uses the XML OSP to access hierarchical data that is stored in an XML document:

  1. Open a new file in Microsoft Notepad. Copy and paste the following code into it, and then save the file as TestOSP.asp in an IIS virtual directory that has permissions to run scripts:
    <%@ Language=VBScript %>

    <!--#include file="" -->

    Dim adoRS 'ADODB.Recordset
    Set adoRS = CreateObject("ADODB.Recordset")

    ' Set up the Connection
    adoRS.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"

    ' Open the XML source
    adoRS.Open Server.MapPath(".") & "\portfolio.xml"

    printtbl adoRS, 0

    If adoRS.State = adStateOpen Then
    End If
    Set adoRS = Nothing

    ' Function to recurcusively retrieve the data
    Sub printtbl(rs, indent)

    Dim rsChild 'ADODB.Recordset
    Dim Col 'ADODB.Field

    set rsChild = Server.CreateObject("ADODB.Recordset")

    While rs.EOF <> True
    For Each Col In rs.Fields
    If Col.Name <> "$Text" Then ' $Text to be ignored
    If Col.Type <> adChapter Then
    ' Output the non-chaptered column
    Response.Write( String((indent)," " ) & Col.Name & ": " & Col.Value )
    ' Retrieve the Child recordset
    Set rsChild = Col.Value
    If Err Then
    Response.write("Error: " & Error )
    end if
    printtbl rsChild, indent + 4
    Set rsChild = Nothing
    End If
    End If
    Response.Write( "<br/>")

    End Sub

  2. The code in the preceding ASP file uses the OLEDB Simple Provider for XML to load data from an XML file named portfolio.xml to an ADO Recordset object. Open a new file in Notepad, and copy and paste the following XML into it. Save the file as portfolio.xml in the IIS virtual directory where you saved TestOSP.asp:
    <companyname>Contoso Pharmaceuticals</companyname>
    <companyname>Fabrikam, Inc</companyname>
    <companyname>Proseware, Inc</companyname>
    <companyname>Wide World Importers</companyname>

  3. Browse to TestOSP.asp with Internet Explorer, and note that the data in portfolio.xml are displayed as required. The code in the ASP uses the XML OSP to load the XML data into an ADO Recordset, and then loops through the records in the recordset and writes out the values in the fields to the ASP Response Object.

Artikelnummer: 272270 – Letzte Überarbeitung: 04.08.2008 – Revision: 1