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

MORE INFORMATION

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.