Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How To Retrieve XML Data in ASP with the XML OLE-DB Simple Provider

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

View products that this article applies to.

Summary

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.

↑ Back to the top

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="adovbs.inc" -->
<%

    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
        adoRS.Close
    End If
    Set adoRS = Nothing
    Response.End 

' 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 )
                Else
                    Response.Write("<br/>")
                    ' Retrieve the Child recordset
                    Set rsChild = Col.Value
                    rsChild.MoveFirst
                    If Err Then 
                        Response.write("Error: " &  Error ) 
                        Response.end
                    end if     
                    printtbl rsChild, indent + 4
                    rsChild.Close
                    Set rsChild = Nothing
                End If
            End If
        Next
        Response.Write( "<br/>")
        rs.MoveNext
    Wend
    
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:
    <portfolio>
    <stock>
        <symbol>CTS</symbol>
        <price>$66.00</price>
        <info>
            <companyname>Contoso Pharmaceuticals</companyname>
            <website>http://www.contoso.com</website>
        </info>
    </stock>
    <stock>
        <symbol>FAB</symbol>
        <price>$110.00</price>
        <info>
            <companyname>Fabrikam, Inc</companyname>
            <website>http://www.fabricam.com</website>
        </info>
    </stock>
    <stock>
        <symbol>PRO</symbol>
        <price>$50.00</price>
        <info>
            <companyname>Proseware, Inc</companyname>
            <website>http://www.proseware.com</website>
        </info>
    </stock>
    <stock>
        <symbol>WWI</symbol>
        <price>$136.00</price>
        <info>
            <companyname>Wide World Importers</companyname>
            <website>http://www.worldwideimporters.com</website>
        </info>
    </stock>
</portfolio>
  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.

↑ Back to the top

Keywords: KB272270, kbhowto

↑ Back to the top

Article Info
Article ID : 272270
Revision : 4
Created on : 7/13/2004
Published on : 7/13/2004
Exists online : False
Views : 642