Microsoft KB Archive/281099

= How To Use Active Server Pages to Dynamically Generate XML =

Article ID: 281099

Article Last Modified on 11/22/2005

-

APPLIES TO


 * Microsoft Active Server Pages 4.0
 * Microsoft Internet Information Server 4.0
 * Microsoft Internet Information Services 5.0

-



This article was previously published under Q281099



SUMMARY
This article describes how to use Active Server Pages (ASP) to dynamically create eXtensible Markup Language (XML). The example uses an XSL style sheet to format the results.

NOTE: The sample only works in XML-enabled browsers such as Microsoft Internet Explorer 5 or later.

NOTE: The sample should be considered a starting point in learning and understanding how to output XML from ASP.



MORE INFORMATION
WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code &quot;as is&quot; without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

ASP/XML Example
This article is divided into the following sections:


 * Create a system DSN to connect to the database.
 * Create the XSL style sheet to format the data.
 * Create the XML generator to output the data.
 * Test the pages to verify the functionality of the sample.

A System Data Source Name (DSN) provides a way for ASP pages and other data clients to interface with a database.

NOTE: You have to be an administrator to create a System DSN.
 * 1) Create a new folder named WebData within the folder that contains the content for your default Web site. This is the :\InetPub\WWWRoot folder by default.
 * 2) Copy the NorthWind sample database (Nwind.mdb) to this new folder. To install the NorthWind sample database, install the samples that come with Microsoft Visual Studio 6.0 and Microsoft Office. Visual Studio 6.0 Setup installs Nwind.mdb in :\Program Files\Microsoft Visual Studio\VB98 by default.
 * 3) On Microsoft Windows 2000, click Start, point to Programs, point to Administrator Tools, and then click Open Data Sources (ODBC).

On Microsoft Windows 95, Microsoft Windows 98, and Microsoft Windows NT, open Data Sources (ODBC) in Control Panel.
 * 1) On the System DSN tab, click Add.
 * 2) Click Microsoft Access Driver (*.mdb), and then click Finish.
 * 3) Click Select. In the WebData folder, click the NorthWind database, and then click OK.
 * 4) In the Data Source Name text box, type NORTHWIND, and then click OK.
 * 5) Click OK to exit Data Sources (ODBC).

To better view the XML data, create an XSL style sheet to format the data.   In Notepad, paste the following XSL code:     XML TEST    </xsl:template> </xsl:stylesheet> </li> <li>Save the page as Xmltest.xsl in the WebData folder.</li></ol>

Because ASP can format data from a database in many ways, you can output XML from data that is found in the database. <ol> <li> In Notepad, paste the following XSL code: <% @ Language=&quot;VBScript&quot; %> <% ' Declare all variables. Option Explicit Dim objCN,objRS,objField Dim strSQL,strCN Dim strName,strValue

' Buffer and output as XML. Response.Buffer = True Response.ContentType = &quot;text/xml&quot;

' Start our XML document. Response.Write &quot;<?xml version=&quot;&quot;1.0&quot;&quot;?>&quot; & vbCrLf Response.Write &quot;<?xml:stylesheet type=&quot;&quot;text/xsl&quot;&quot; href=&quot;&quot;xmltest.xsl&quot;&quot;?>&quot; & vbCrLf

' Set SQL and database connection string. strSQL = &quot;SELECT * FROM Customers&quot; strCN = &quot;DSN=NORTHWIND&quot;

' Open the database connection and recordset. Set objCN = Server.CreateObject(&quot;ADODB.Connection&quot;) objCN.Open strCN Set objRS = objCN.Execute(strSQL)

' Output start of data. Response.Write &quot;<DATABASE>&quot; & vbCrLf

' Loop through the data records. While Not objRS.EOF ' Output start of record. Response.Write &quot;<RECORD>&quot; & vbCrLf ' Loop through the fields in each record. For Each objField in objRS.Fields strName = objField.Name strValue = objField.Value If Len(strName) > 0 Then strName = Server.HTMLEncode(strName) If Len(strValue) > 0 Then strValue = Server.HTMLEncode(strValue) Response.Write &quot;<FIELD>&quot; & vbCrLf Response.Write &quot;<NAME>&quot; & strName & &quot;</NAME>&quot; & vbCrLf Response.Write &quot;<VALUE>&quot; & strValue & &quot;</VALUE>&quot; & vbCrLf Response.Write &quot;</FIELD>&quot; & vbCrLf Next ' Move to next record in database. objRS.MoveNext ' Output end of record. Response.Write &quot;</RECORD>&quot; & vbCrLf Wend

' Output end of data. Response.Write &quot;</DATABASE>&quot; & vbCrLf %>                   </li> <li>Save the page as Xmltest.asp in the WebData folder.</li></ol>

In Internet Explorer, type http://localhost/WebData/xmltest.asp in the address bar. An HTML table with the information from the Customers table in the sample database appears. However, if you view the source code to the page, you see only the XML data that the ASP page generated. This occurs because Internet Explorer loads the XML data and then retrieves the XSL style sheet to format the output that actually appears.

In addition, both the XML data and the XSL style sheet are generic in design to allow easy data customization. For example, you could modify the SQL text as follows: strSQL = &quot;SELECT * FROM Employees&quot; This changes the database table that is used, and the code sample still functions properly.

<div class="references_section">