Edit

XML Recordset Persistence Scenario

  • Article

In this scenario, you will create an Active Server Pages (ASP) application that saves the contents of a Recordset object directly to the ASP Response object.

Note

This scenario requires that your server have Internet Information Server 5.0 (IIS) or later installed.

The returned Recordset is displayed in Internet Explorer using a DataControl Object (RDS).

The following steps are necessary to create this scenario:

  • Set Up the Application

  • Get the Data

  • Send the Data

  • Receive and Display the Data

Step 1: Set Up the Application

Create an IIS virtual directory named "XMLPersist" with script permissions. Create two new text files in the folder to which the virtual directory points, one named "XMLResponse.asp," the other named "Default.htm."

Step 2: Get the Data

In this step, you will write the code to open an ADO Recordset and prepare to send it to the client. Open the file XMLResponse.asp with a text editor, such as Notepad, and insert the following code.

<%@ language="VBScript" %>  
  
<!-- #include file='adovbs.inc' -->  
  
<%  
  Dim strSQL, strCon  
  Dim adoRec   
  Dim adoCon   
  Dim xmlDoc   
  
  ' You will need to change "MySQLServer" below to the name of the SQL   
  ' server machine to which you want to connect.  
  strCon = "Provider=sqloledb;Data Source=MySQLServer;Initial Catalog=Pubs;Integrated Security=SSPI;"  
  Set adoCon = server.createObject("ADODB.Connection")  
  adoCon.Open strCon  
  
  strSQL = "SELECT Title, Price FROM Titles ORDER BY Price"  
  Set adoRec = Server.CreateObject("ADODB.Recordset")  
  adoRec.Open strSQL, adoCon, adOpenStatic, adLockOptimistic, adCmdText

Be sure to change the value of the Data Source parameter in strCon to the name of your Microsoft SQL Server computer.

Keep the file open and go on to the next step.

Step 3: Send the Data

Now that you have a Recordset, you must send it to the client by saving it as XML to the ASP Response object. Add the following code to the bottom of XMLResponse.asp.

  Response.ContentType = "text/xml"  
  Response.Expires = 0  
  Response.Buffer = False  
  
  Response.Write "<?xml version='1.0'?>" & vbNewLine  
  adoRec.save Response, adPersistXML  
  adoRec.Close  
  Set adoRec=Nothing  
%>

Notice that the ASP Response object is specified as the destination for the Recordset Save Method. The destination of the Save method can be any object that supports the IStream interface, such as an ADO Stream Object (ADO), or a file name that includes the complete path to which the Recordset is to be saved.

Save and close XMLResponse.asp before going to the next step. Also copy the adovbs.inc file from the default ADO library installation folder to the same folder where you saved the XMLResponse.asp file.

Step 4: Receive and Display the Data

In this step you will create an HTML file with an embedded DataControl Object (RDS) object that points at the XMLResponse.asp file to get the Recordset. Open default.htm with a text editor, such as Notepad, and add the following code. Replace "sqlserver" in the URL with the name of your server.

<HTML>  
<HEAD><TITLE>ADO Recordset Persistence Sample</TITLE></HEAD>  
<BODY>  
  
<TABLE DATASRC="#RDC1" border="1">  
  <TR>  
<TD><SPAN DATAFLD="title"></SPAN></TD>  
<TD><SPAN DATAFLD="price"></SPAN></TD>  
  </TR>  
</TABLE>  
<OBJECT CLASSID="clsid:BD96C556-65A3-11D0-983A-00C04FC29E33" ID="RDC1">  
   <PARAM NAME="URL" VALUE="XMLResponse.asp">  
</OBJECT>  
  
</BODY>  
</HTML>

Close the default.htm file and save it to the same folder where you saved XMLResponse.asp. Using Internet Explorer 4.0 or later, open the URL https://sqlserver/XMLPersist/default.htm and observe the results. The data is displayed in a bound DHTML table. Now open the URL https:// sqlserver /XMLPersist/XMLResponse.asp and observe the results. The XML is displayed.

See Also

Save Method
Persisting Records in XML Format