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.

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 : 275