Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 272270 - Last Review: July 13, 2004 - Revision: 2.2
How To Retrieve XML Data in ASP with the XML OLE-DB Simple Provider
This article was previously published under Q272270
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.
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:
- 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
%>
- 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>
- 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.
APPLIES TO
- Microsoft ActiveX Data Objects 2.6
- Microsoft ActiveX Data Objects 2.7
- Microsoft Data Access Components 2.6
- Microsoft Data Access Components 2.7
- Microsoft Active Server Pages 4.0
- Microsoft XML Parser 2.6
- Microsoft XML Parser 3.0
- Microsoft XML Core Services 4.0
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate