18.3.4- Creating XML from a Relational Database

by NT Community Manager.
Last Updated by Jim Minatel.

Public Categorized as 18. An Introduction to XML.

Not tagged.

<< 18.3.3- Creating XML Documents from a Web Page

Creating XML from a Relational Database

So far, we have been talking about XML documents that reside in files on the server, rather like our HTML or ASP files. However, there are many situations where these are not going to be the ideal storage format for our data. For example:

Size: We have been dealing with fairly small documents, with a limited number of records. However, if you had several hundred or thousands of records in an XML document, you would end up with a very large document to navigate or pass around the network. Databases are better suited to some kinds of information storage, especially large quantities of data that are not all required at the same time.
Concurrency: If we are working with an XML document on a file system, it is very difficult to control who can edit the document. For example, you may only allow one person to view and edit the file at a time, or you may end up with two people editing the file, and only one person's changes taking effect.
Security: It is hard to control what parts of a document different people can see or modify.

Relational databases can handle these problems a lot easier than we can with XML files. So, we should have a look at creating some XML data from a database.

Indeed, whether there are advantages or not, many businesses already have a lot of information held in relational databases anyway. If we are either writing systems that are based on XML, or want to take advantage of the ability to pass data around as XML we need a way of generating XML from the data stored in relational databases. Luckily we can do this quite easily with ASP.

There are different ways we could approach this problem:

Using ASP and string manipulation
Using ASP and the DOM
Using ADO 2.5 to create the XML

Let's look at using string manipulation first, as it is the easiest way.

We have provided a sample Access database with the downloads for this chapter so that you can get started without having to create your own table. It is called Reports.mdb

Collecting Table of Reports

The database is holding details of old reports, of the type we created in the previous section. They are stored in full as text-based XML files. Here, we have the dailyReports table, from the Reports database. We are holding a unique ID for each file, the short name for the department in the Dept column, the date of the report in the Date column, the main body of the report in the FullReport column and some summary information about the report in the KeyInfo column:

Chapter18_image018

From this, we want to create the following XML file from the database:

<?xml version="1.0" ?>

<intro> This is a list of reports on the Wrox Finance site using ASP to create XML from database content </intro>

<keyInfo>Summary</keyInfo>

</report>

</dailyReports>

So, let's see how we create the XML using ASP.

Try It Out – Creating XML from a Database

1. In order to connect to the database we are going to use our trusty datastore.asp file, that we created in Chapter 12 . You should find it in your BegASPFiles directory. Create a copy of this file in your XMLFiles folder then open up this copy and make sure it looks something like this:

Dim strDatabaseType

'Choose one of the following two lines, and comment out the other

strDatabaseType = "Access"

'strDatabaseType = "SQLServer"

'Now we use this selection to specify the connection string

If strDatabaseType = "Access" Then

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\datastores\reports.mdb;" & _

"Persist Security Info=False"

Else

strConnect = "Provider= SQLOLEDB;Persist Security Info=False;" & _

"User ID=sa;Initial Catalog=Movie2000;" & _

"Initial File Name=C:\MSSQL7\Data\Movies2000.mdf"

End If

2. Basically we need an ASP page that will connect to our reports database and create XML from the database content, giving us a summary list of reports held in the database. Open up a fresh file in your text editor and enter the following code:

<%@LANGUAGE="VBScript"%>

<% Response.ContentType = "text/xml" %>

<% strDate = Year(Now) & "-" & Right("00" & Month(Now), 2) & "-" &_

Right("00" & Day(Now), 2) %>

<?xml version="1.0" ?>

<intro>This is a list of reports on the Wrox Finance site using ASP to create XML from database content</intro>

<% '-- select all the report details --

QUOT = Chr(34)

On Error Resume Next

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open strConnect

strSQL="SELECT * FROM dailyReports ORDER BY Dept"

Set oRs = oConn.Execute(strSQL)

Do While Not oRs.EOF

strDate = oRs("Date")

strDept = oRs("Dept")

strKeyInfo = oRs("KeyInfo")

</report>

oRs.MoveNext

Loop

oRs.Close

oConn.Close

Set oConn = Nothing

Set oRs = Nothing

</dailyReports>

3. Save the file as makeList.asp, and save it in the file with the other samples.

4. Open up your browser, and choose the file. You should get something like this.

Chapter18_image019

How It Works

Our makeXML.asp page starts by setting the language that we are using. Then we have our include command that inserts the connection string definition from datastore.asp. We then set the ContentType property of the Response object to ensure that we have the correct headers written back to the client. As we will be sending back XML, the value is "text/xml". This allows us to write XML back to the client rather than HTML outside of the ASP delimiters:

<%@LANGUAGE="VBScript"%>

<% Response.ContentType = "text/xml" %>

We then build up the current date using VBScript functions, and set them to a variable strDate (this will allow us to write the date to the dateCreated attribute of the root <dailyReports> tag):

<% strDate = Year(Now) & "-" & Right("00" & Month(Now), 2) & "-" & _

Right("00" & Day(Now), 2) %>

Next, we write the XML prolog back to the client, and the root <dailyReports> tag. The root tag has a dateCreated attribute, which takes the value of the date created in the last section:

<?xml version="1.0" ?>

The result of the second line will be an XML tag with the current date in it:

This shows you how we can mix the XML tags with ASP. This is exactly the same as the way that we write HTML tags to the client page when using ASP. Since we have set the ContentType property of the Response object to "text/xml", the receiving application knows that we are sending it as XML rather than HTML. Therefore, when we have the strDate variable in between the ASP delimiters, we will just be writing back the value of the strDate variable to the client as the content of the tags.

We also have an <intro> tag, which holds some text, which we will use to display on the browser, as an introduction to the information we are displaying for the list that we are creating:

<intro>This is a list of reports on the Wrox Finance site using ASP to create XML from database content</intro>

We then have to establish a connection to the database that holds the data we want to transform into XML, in this case the Reports database. It should come as no surprise that the first thing we do is to create an ADO Connection object. We store this in the variable oConn. And then we open the Connection object held in this variable, passing in the appropriate connection details:

<% '-- select all the report details --

QUOT = Chr(34)

On Error Resume Next

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open strConnect

We want to retrieve information from the dailyReports table in the database. To do this we need to compose a SQL query:

strSQL="SELECT * FROM dailyReport ORDER BY File"

The variable strSQL is set to hold the query that retrieves all of the information from the dailyReport table.

Next, we implicitly create a Recordset object in which to place all of the table's information. We execute the SQL query held in the strSQL variable SQL using the Execute method of our Connection object. We assign the results to our oRs variable:

Set oRs = oConn.Execute(strSQL)

We then navigate through each record and, as we go, we set four variables to hold the date, department, summary, and file name:

Do While Not oRs.EOF

strDate = oRs("Date")

strDept = oRs("Dept")

strKeyInfo = oRs("KeyInfo")

strFile = oRs("File")

The query will go through the records until the end of the table is found, using the end of file method EOF. This is because we have started a Do...While function, which allows us to loop through the following procedure for each record in the database. The Loop command comes later, after we have written the XML for the current entry.

To create the XML from the database we use the same technique as that used to insert the date in the dateCreated attribute of the dailyReports root element. First we write the opening <report> tag back to the client, followed by the opening <date> tag. Then we write the value of the strDate variable that holds the date for the current record back to the client as the content of the <date> element. We do the same for the <dept> and <keyInfo> elements. After retrieving the information, we just write in the closing </report> tag:

</report>

So far, this will write one record of XML back to the client. We now have to loop through the remaining records, until the end of the file is found, and write a record for each of them. We then close the recordset and the database connection and clean up after ourselves, to save system resources.

'loop through the records creating XML

oRs.MoveNext

Loop

oRs.Close

oConn.Close

Set oConn = Nothing

Set oRs = Nothing

Finally we write out the closing </dailyReport> root element tag.

</dailyReports>

Let's now have a brief look at how we could achieve the same thing using ADO 2.5 to create the XML.

Using ADO 2.5 to Create XML from Databases

With ADO 2.5 Microsoft allows us to create XML from database content and save it to a file or return it as a stream (a stream being a block of data in memory that is not processed), see Chapter 14 . However, at the time of writing, ADO would only create XML as element content. For example:

<z:row id="1" dept="Pharm" date="1999-10-11"

Full_Report="Full Pharm Report is here… "

KeyInfo="Summary info for Pharm dept" />

<z:row id="2" dept="Petro" date="1999-10-11"

Full_Report="Full Petro Report is here… "

KeyInfo="Summary info for Petro dept" />

There is no reason why you should not use this format. Indeed, it can be considered beneficial as the network traffic is less. However, at the time of writing, it allowed no flexibility in the format in which it creates this output. So, it depends upon the way in which you want to structure your data, and on the DTD you are using, as to whether this format is suitable to you. If this will suffice you can learn how to use it from either Professional ADO 2.5 (ISBN 1-861002-75-0) or Professional ASP 3.0 (1-861002-61-0) – both published by Wrox Press. (This book is no longer available from Wrox but you might find it from a store that sells used books.) Information is also available at the Microsoft web site: http://msdn.Microsoft.com.

So far in this chapter we have seen several ways of creating XML and we have seen how to display it using CSS. IE5 also provides a helpful way of displaying and navigating through our XML documents, using databinding in a standard HTML page.

<< 18.3.3- Creating XML Documents from a Web Page

Chapter18

18.3.5- Databinding >>