If you're new here, you may want to subscribe to my RSS feed. So that you can read the latest updates about Web2.0 tools, Making Money Online, Tips in SEO, Ajax and many more. Thanks for visiting ProgramimiCOM!

An XML data island is a piece of well-formed XML embedded into an HTML file. This article will show you how to retrieve data in an XML format from a database using ADO; you will also learn how to bind this data into an HTML document.Introduction

The previous tutorial, The Why and How of XML Data Islands, considered embedding a well formed XML fragment into an HTML file to create an XML data island. The article also showed how one could access data embedded in the XML file. The tutorial also described data binding to various HTML tags. However the XML data used was a hard-coded XML fragment.

In this tutorial, how data in XML format can be retrieved from a database using ADO will be described. The XML data obtained using ADO will be reviewed. An example of how Jet Data types are associated with XML data types will be shown. This will be followed by how to use the data to bind it to an HTML document. After all, this is what the client is after. It will be helpful if the reader reviews the previous article and the XML related articles to which you can find links on this site.

Data to XML Conversion using ADO

Persisting data in the XML format is one of the most important features of ADO since the 2.5 version. This means that ADO recordsets can be saved as an XML file to a location of your choice. Alternatively they can also be saved to a Stream object. Since the data is derived from a database together with the data, the data structure comes with it. Ideally this format should be able to be transparently used by any machine.

Displaying ADO Retrieved Data with XML Islands - Extracting XML formatted data example

Using the save() method of ADO, you could save the recordset to a file as shown in the next paragraph. The recordset is created by accessing an MDB file on the hard drive. Not all the columns in the ‘Employees‘ table in the Northwind database will be saved as an XML file. The variable fileName points to the location on the hard drive where the XML file will be saved.

Create UI to test code

On a form in your MS Access application add a button, and to the click event of this button add the following code. The statement

rs.save fileName, adPersistXML >

can also be saved as

rs.save fileName2, adPersistADTG

where fileName2 will have an .adtg extension. This is yet another proprietary format called the Advanced Data TableGram format. We will only look at the persisted XML formatted file.

Private Sub Command0_Click()

Dim rs As New ADODB.Recordset

fileName = "C:NwindEmployees.xml"

rs.Open "Select * from Employees where LastName='Peacock'", _

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

"Data Source=C:Documents and SettingsJayMy DocumentsRetrieve.mdb;" & _

"Persist Security Info=False", adOpenKeyset, adLockOptimistic,

adCmdText

If Dir$(fileName) <> "" Then Kill fileName

rs.Save fileName, adPersistXML

End Sub

In the above code the recordset for the indicated SQL statement will be saved.

Displaying ADO Retrieved Data with XML Islands - The Saved XML file

If you open the file C:NwindEmployees.xml in a text editor you can see the full details.

<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'

        xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'

        xmlns:rs='urn:schemas-microsoft-com:rowset'

        xmlns:z='#RowsetSchema'>

<s:Schema id='RowsetSchema'>

        <s:ElementType name='row' content='eltOnly' rs:updatable='true'>

               <s:AttributeType name='Address' rs:number='8'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='Address'>

                       <s:datatype dt:type='string' dt:maxLength='60'/>

               </s:AttributeType>

               <s:AttributeType name='BirthDate' rs:number='6'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='BirthDate'>

                       <s:datatype dt:type='DateTime'rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='City' rs:number='9'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='City'>

                       <s:datatype dt:type='string' dt:maxLength='15'/>

               </s:AttributeType>

               <s:AttributeType name='Country' rs:number='12'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='Country'>

                       <s:datatype dt:type='string' dt:maxLength='15'/>

               </s:AttributeType>

               <s:AttributeType name='EmployeeID' rs:number='1'rs:maydefer='true' rs:writeunknown='true' rs:basetable='Employees'

                        rs:basecolumn='EmployeeID'rs:autoincrement='true'>

                       <s:datatype dt:type='int' dt:maxLength='4'rs:precision='10' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='Extension' rs:number='14'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='Extension'>

                       <s:datatype dt:type='string' dt:maxLength='4'/>

               </s:AttributeType>

               <s:AttributeType name='FirstName' rs:number='3'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='FirstName'>

                       <s:datatype dt:type='string' dt:maxLength='10'/>

               </s:AttributeType>

               <s:AttributeType name='HireDate' rs:number='7'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='HireDate'>

                       <s:datatype dt:type='dateTime'rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='HomePhone' rs:number='13'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='HomePhone'>

                       <s:datatype dt:type='string' dt:maxLength='24'/>

               </s:AttributeType>

               <s:AttributeType name='LastName' rs:number='2'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='LastName'>

                       <s:datatype dt:type='string' dt:maxLength='20'/>

               </s:AttributeType>

               <s:AttributeType name='Notes' rs:number='16'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='Notes'>

                       <s:datatype dt:type='string'dt:maxLength='536870910' rs:long='true'/>

               </s:AttributeType>

               <s:AttributeType name='Photo' rs:number='15'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='Photo'>

                       <s:datatype dt:type='string' dt:maxLength='255'/>

               </s:AttributeType>

               <s:AttributeType name='PostalCode' rs:number='11'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='PostalCode'>

                       <s:datatype dt:type='string' dt:maxLength='10'/>

               </s:AttributeType>

               <s:AttributeType name='Region' rs:number='10'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='Region'>

                       <s:datatype dt:type='string' dt:maxLength='15'/>

               </s:AttributeType>

               <s:AttributeType name='ReportsTo' rs:number='17'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='ReportsTo'>

                       <s:datatype dt:type='int' dt:maxLength='4'rs:precision='10' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='Title' rs:number='4'rs:nullable='true' rs:maydefer='true' rs:write='true'rs:basetable='Employees'

                        rs:basecolumn='Title'>

                       <s:datatype dt:type='string' dt:maxLength='30'/>

               </s:AttributeType>

               <s:AttributeType name='TitleOfCourtesy'rs:number='5' rs:nullable='true' rs:maydefer='true' rs:write='true'

                        rs:basetable='Employees'rs:basecolumn='TitleOfCourtesy'>

                       <s:datatype dt:type='string' dt:maxLength='25'/>

               </s:AttributeType>

               <s:extends type='rs:rowbase'/>

        </s:ElementType>

</s:Schema>

<rs:data>

        <z:row Address='4110 Old Redmond Rd.'BirthDate='1958-09-19T00:00:00' City='Redmond' Country='USA'EmployeeID='4'

                Extension='5176' FirstName='Margaret'HireDate='1993-05-03T00:00:00' HomePhone='(206) 555-8122'LastName='Peacock'

                Notes='Margaret holds a BA in English literature fromConcordia College and an MA from the American Institute of Culinary Arts.She was temporarily assigned to the London office before returning to her

permanent post in Seattle.'

                Photo='EmpID4.bmp' PostalCode='98052' Region='WA'ReportsTo='2' Title='Sales Representative' TitleOfCourtesy='Mrs.'/>

</rs:data>

</xml>

Displaying ADO Retrieved Data with XML Islands - Reviewing the saved file

Although the file is large, it consists basically of two parts, as shown in the browser display of this file after collapsing all the details. As seen in the next picture, the file’s two parts are the ‘Schema’ and the ‘Data’ represented by their prefixes, ‘s:‘ and ‘rs:‘ as shown in the namespaces — the first four lines in the document which have the prefix xmlns.

The schema section

The next picture shows just one element from the expanded ‘s‘ node in the displayed XML file in the browser. You can also see that it is updatable. This particular slice corresponds to the ‘Address‘ field of the Employees table shown in the first picture. The fields are listed alphabetically in the persisted file. The other elements also show the various attributes of the Address field. In the original table, the Address field’s Data type is text and field size is 60. The XML attribute with the prefix ‘dt’ which marks the beginning of each row shows this information. The ‘text’ has become ’string’ and the field size has become ‘maxlength.’ The schema information therefore is an accurate representation of the data structure.

The Data Section

The next section shows the only row of data taken from the data section. The prefix ‘z’ marks the beginning of the data. The XML file has only one row of the table returned corresponding to the LastName=’Peacock.‘

Displaying ADO Retrieved Data with XML Islands - Data types in Access 2003 and XML file

In MS Access there are several data types that are typical to its Jet Library. In order to look at how the data goes over into XML, a table, called ‘Whimsical‘ was contrived which has all the data types but contains only a single row of data. This was opened just like the previous table and the saved file was examined. In the next picture you see the table and in the one that follows, you see the ’schema’ section. The data types, ‘bitmap’, and ‘hyperlink’ can take up a large amount of space. This one row of data saved to file takes up as much as 0.5 MB.

This is the ‘Schema’ section of the above file. Review each of the data types and you’ll see the corresponding dt:type in the XML file.

Displaying ADO Retrieved Data with XML Islands - Displaying retrieved XML in an HTML document

Creating an XML Data Island

From the previous tutorial we know that we need to embed the XML in an XML document to produce the XML Data Island. The ADO’s save() method does not produce a data island. This can be built in two steps. First of all, to associate the ‘Data’ contained in the XML to the bondable tags of the HTML, we need a basis of association. This is given by the following XML block with the id=’test.’

<XML id="test">

</XML>

In the second step, you will notice that XML is already the first tag in the saved file. Since you cannot have two XML tags, you modify the saved file by prefixing ado to xml and changing it to adoxml as shown. This will be embedded in the previous block and the resulting XML is the XML Data Island.

<adoxml xmlns:s=’uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882′

        xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'

        xmlns:rs='urn:schemas-microsoft-com:rowset'

        xmlns:z='#RowsetSchema'>

<s:Schema id='RowsetSchema'>

        <s:ElementType name='row' content='eltOnly' rs:updatable='true'>

               <s:AttributeType name='Address' rs:number='8'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='Address'>

                       <s:datatype dt:type='string' dt:maxLength='60'/>

               </s:AttributeType>

               <s:AttributeType name='BirthDate' rs:number='6'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='BirthDate'>

                       <s:datatype dt:type='dateTime'rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='City' rs:number='9'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='City'>

                       <s:datatype dt:type='string' dt:maxLength='15'/>

               </s:AttributeType>

               <s:AttributeType name='Country' rs:number='12'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='Country'>

                       <s:datatype dt:type='string' dt:maxLength='15'/>

               </s:AttributeType>

               <s:AttributeType name='EmployeeID' rs:number='1'rs:maydefer='true' rs:writeunknown='true' rs:basetable='Employees'

                        rs:basecolumn='EmployeeID'rs:autoincrement='true'>

                       <s:datatype dt:type='int' dt:maxLength='4'rs:precision='10' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='Extension' rs:number='14'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='Extension'>

                       <s:datatype dt:type='string' dt:maxLength='4'/>

               </s:AttributeType>

               <s:AttributeType name='FirstName' rs:number='3'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='FirstName'>

                       <s:datatype dt:type='string' dt:maxLength='10'/>

               </s:AttributeType>

               <s:AttributeType name='HireDate' rs:number='7'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='HireDate'>

                       <s:datatype dt:type='dateTime'rs:dbtype='variantdate' dt:maxLength='16' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='HomePhone' rs:number='13'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='HomePhone'>

                       <s:datatype dt:type='string' dt:maxLength='24'/>

               </s:AttributeType>

               <s:AttributeType name='LastName' rs:number='2'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='LastName'>

                       <s:datatype dt:type='string' dt:maxLength='20'/>

               </s:AttributeType>

               <s:AttributeType name='Notes' rs:number='16'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='Notes'>

                       <s:datatype dt:type='string'dt:maxLength='536870910' rs:long='true'/>

               </s:AttributeType>

               <s:AttributeType name='Photo' rs:number='15'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='Photo'>

                       <s:datatype dt:type='string' dt:maxLength='255'/>

               </s:AttributeType>

               <s:AttributeType name='PostalCode' rs:number='11'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='PostalCode'>

                       <s:datatype dt:type='string' dt:maxLength='10'/>

               </s:AttributeType>

               <s:AttributeType name='Region' rs:number='10'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='Region'>

                       <s:datatype dt:type='string' dt:maxLength='15'/>

               </s:AttributeType>

               <s:AttributeType name='ReportsTo' rs:number='17'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='ReportsTo'>

                       <s:datatype dt:type='int' dt:maxLength='4'rs:precision='10' rs:fixedlength='true'/>

               </s:AttributeType>

               <s:AttributeType name='Title' rs:number='4'rs:nullable='true' rs:maydefer='true' rs:write='true'

rs:basetable='Employees'

                        rs:basecolumn='Title'>

                       <s:datatype dt:type='string' dt:maxLength='30'/>

               </s:AttributeType>

               <s:AttributeType name='TitleOfCourtesy' rs:number='5'rs:nullable='true' rs:maydefer='true' rs:write='true'

                        rs:basetable='Employees'rs:basecolumn='TitleOfCourtesy'>

                       <s:datatype dt:type='string' dt:maxLength='25'/>

               </s:AttributeType>

               <s:extends type='rs:rowbase'/>

        </s:ElementType>

</s:Schema>

<rs:data>

        <z:row Address='4110 Old Redmond Rd.'BirthDate='1958-09-19T00:00:00' City='Redmond' Country='USA'

EmployeeID='4'

                Extension='5176' FirstName='Margaret'HireDate='1993-05-03T00:00:00' HomePhone='(206) 555-8122'

LastName='Peacock'

                Notes='Margaret holds a BA in English literature fromConcordia College and an MA from the American Institute of Culinary Arts.

She was temporarily assigned to the London office before returning to her

permanent post in Seattle.'

                Photo='EmpID4.bmp' PostalCode='98052' Region='WA'ReportsTo='2' Title='Sales Representative' TitleOfCourtesy='Mrs.'/>

</rs:data>

</adoxml>

Displaying ADO Retrieved Data with XML Islands - Creating an HTML document which can display the XML Data

AdoIsland.html

In the code shown next, insert the XML data island created in the previous section and save it as AdoIsland.htm. Make sure the DATASRC attribute of the table corresponds to the ID field of the XML Data Island. The DATAFIELD attribute refers respectively to the data fields in the XML data island.

<html><head><title></title></head>

<body>

<table DATASRC="#Test“>

<tr>

<tr><td>

<table DATAsrc=”#Test” DATAFLD=”rs:data” border=”1″

bgcolor="yellow">

<tr><td>

<table DATAsrc=”#Test” DATAFLD=”z:row”>

<tr>

<td><span DATAFLD=”EmployeeID”></span</td>

<td><span DATAFLD=”FirstName”></span</td>

<td><span DATAFLD=”LastName”></span</td>

</tr>

</table>

</td></tr>

</table>

</td></tr>

</table>

<!–Here plug in the XML Data Island –>

</body>

</html>

Displaying the XML Data retrieved on the IE

Now if you browse the AdoIsland.htm file the display should appear as follows. If you refer to the table above, you can see that although all fields are in the XML Data Island, only three of them are called while displaying.

Displaying the image saved as an embedded BMP file

From the XML file you could retrieve the field for the ‘Photo’ and display the image on the browser. For this, you need to add another cell to the table and insert the following for the cell.

<td><img src=”"  DATAFLD=”Photo”></td>

With the above table cell added to the table, the display now appears as shown here. For this to display correctly you should have the corresponding BMP file at the same location as the AdoIsland.htm file.

Summary

This tutorial extends the previous tutorial to show how to access data and display it using the ADO’s save() method. An example to display the persisted data using the XML Data Island was described. More importantly, the data types in MS Access as they relate to data types in the persisted XML file were shown in detail. Similar ideas will help in accessing data from a web server with Active Server Pages as well.