In this article, I’ll show you one of the greatest features about ADO 2.5, which allows us to access a recordset as an XML document, providing a different way to manipulate data.
ADO Recordsets Stored As XML
We’ll start today by exploring how ADO presents its data as XML, looking specifically at XML and ADO integration. We’ll look at ways to get XML data out of ADO in a bit, but right now we need to discuss the format of the XML data.
If we take a look at a section of the Employee table (having the following fields: emp_id, emp_lname, emp_fname, address, city, state, zip, phone, contract), converted directly from ADO, we’re in for a little surprise.
<Z: address="”123" contract="“false”" 654-9878”
phone="“498" zip="“33054”" state="“FL”"
city="“Sunrise”" Drive” Orange
au_fname="“William”"
emp_lname="“Scott”" emp_id="“101”" row />
<Z: address="”123" contract="“false”" phone="“561"
zip="“33039”"
state="“FL”" city="“Boca”" au_fname="“Harrison”"
emp_lname="“Ford”"
emp_id="“102”" row 654-0989” St.” Bay Green />
<Z: address="”123" contract="“false”" 654-9878”
phone="“498"
zip="“30334.”" state="“FL”" city="“Miami”" Drive”
au_fname="“Hayes”"
emp_lname="“John”" emp_id="“103”" row Ocean
/>
The output does not really look like the XML document we are used to. Instead of an element for each field we have an element for each row, and the fields are attributes of the rows element. The main reason why Microsoft introduced this method is because of the verbose nature of element-based XML document. When we are retrieving a large recordset converted to XML using only elements, it becomes a handful! This is because every field has a start tag and end tag; dealing with this way of data definition using attributes for each data item means that some repetition can be reduced.
ADO Recordset Namespace
If you’re using ADO to send and retrieve data then you’ll see a schema associated with the file. The schema is embedded into the XML and placed at the top of the document. The first thing which stands out in the code is the root element, which identifies several namespaces.
<xml xmlns:s="“uuid:BCD6E3FO-6DA3-11dl-A2A3-00AAOOC14882”" xmlns:dt="“uuid:C2F41010-65BC-11dl-A29F-00AAOOC14882”" xmlns:rs="“urn:schema-microsoft-com:rowset”" xmlns:z="“#RowsetSchema”"
Now let us look what these namespaces represent by looking at the table below:
| Namespace | Description |
|---|
| s | Identifies the URI for the schema itself. |
| dt | Identifies the URI for the data types. |
| rs | Identifies the rowset (recordset). |
| z | Identifies the individual rows. |
Using namespaces will ensure that the element names chosen by Microsoft are applied to the schema correctly.
ADO Recordset Schema
Since we have namespaces to define the unique properties in the schema and recordset, let us examine a schema itself for the Employee table.
<s:Schema id = “RowsetSchema”>
<s:ElementType name = “row” content = “eltonly”>
<s:AttributeType name = “emp_id” rs:number= “1” rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “12” rs:maybenull = “false” />
</s:AttributeType>
<s:AttributeType name = “emp_lname” rs:number= “2” rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “40” rs:maybenull = “false” />
</s:AttributeType>
<s:AttributeType name = “emp_fname” rs:number= “3” rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “25” rs:maybenull = “false” />
</s:AttributeType>
<s:AttributeType name = “address” rs:number= “4” rs:maybenull = “true”
rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “45” />
</s:AttributeType>
<s:AttributeType name = “city” rs:number= “5” rs:maybenull = “true”
rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “20” />
</s:AttributeType>
<s:AttributeType name = “state” rs:number= “6” rs:maybenull = “true”
rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “2” rs:fixedLength= “true />
</s:AttributeType>
<s:AttributeType name = “zip” rs:number= “7” rs:maybenull = “true”
rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “5” rs:fixedLength= “true />
</s:AttributeType>
<s:AttributeType name = “phone” rs:number= “8” rs:writeunknown = “true”>
<s:datatype dt:type= “string” dt:maxLength = “12” rs:fixedLength= “true
rs:maybenull = “true” />
</s:AttributeType>
<s:AttributeType name = “contract” rs:number= “9” rs:writeunknown = “true”>
<s:datatype dt:type= “boolean” dt:maxLength = “2” rs:fixedLength= “true
rs:maybenull = “false” />
</s:AttributeType>
In order to get a good understanding of the basic concepts, we need to look at what various pieces of the document do.
The Row Element
Let’s start with the element that identifies the schema:
<s:Schema id = “RowsetSchema”>
In the above example of we have an XML element called
Schema, the top-level element for the entire schema. Namespace is used to uniquely identify the schema, and we give the schema an attribute called
id. To simplify this we can say it is just like giving any type of data filed ID-that identifies the field. In the Schema, the ID identifies the schema by itself.
Next we move to the element definition, which is done using
ElementType .
<s:ElementType name = “row” content = “eltonly”>
The element has two attributes. The first is
name, and it gives the element a name (in our case, it’s “row”). It is this element in the schema that identifies that row. Second attribute is called
content and it identifies what the element can hold. Here are the possible values:
| Value | Description |
|---|
| empty | This means the element cannot contain any content. |
| textOnly | This means the element can only contain text, and no other element. |
| eltOnly | This means the element can only contain other elements, and no text. |
| both | This means the elements can hold both text and other elements. |
This line of schema identifies a row element that can contain only other elements. We will look at an XML data to get a clear picture.
<z: row emp_id = “101” emp_lname = “Scott” au_fname = “William”
address =”123 Orange Drive” city = “Sunrise” state= “FL” zip= “33054”
phone= “498 654-9878” contract= “false” />
The Field Attributes
When you glance back at the XML data, you’ll notice that the field values are represented as attributes. Our schema too must represent those attributes. This in done in two parts. The first is the definition of the
attribute itself, and the second is the definition of the
data type for that particular attribute.
<s:AttributeType name = “emp_id” rs:number= “1” rs:writeunknown = “true”>
The second part is the
AttributeType, which also has a child element to define the data type of the attribute.
<s:datatype dt:type= “string” dt:maxLength = “12” rs:maybenull = “false” />
The above code snippet represents the
string data type, the maximum length of the data to be “12”, and whether the attributes can take in null values.
Sometimes other data types may contain slightly different information. For example:
<s:datatype dt:type= “boolean” dt:maxLength = “2” rs:fixedLength= “true
rs:maybenull = “false” />
The above code also contains additional information to state whether or not the data type contains fixed length data.
The Data Types
When generating XML from ADO, the data types are automatically created for us. If you intend to authorize XML yourself or create schemas for some existing XML, then you need to add support for data types. This is one of the big advantages of using schemas instead of DTDs. The table below lists all the data types supported by XML-data schemas:
| Type | Description |
|---|
| bin.base64 | A binary object. |
| bin.hex | Hexadecimal octets |
| boolean | 0 or 1 (0 for false, and 1 for true) |
| char | A one character length string |
| int | Integer number. |
| number | Floating point number. |
| float | Floating point number. |
| fixed.14.4 | Fixed with float point number, with up to 14 digits to the left of the decimal place and up to 4 digits to the right. |
| date | An ISO date without time. Format is yyy-mm-dd. |
| datetime | An ISO date with optional time. Format is yyy-mm-ddThh:mm:ss. |
| datetime.tz | An ISO date, optionally with time and timezones. |
| time | An ISO time. Format is hh:mm:ss. |
| time.tz | An ISO time with optional timezone. |
| i1 | An 8 bit integer. |
| i2 | A 16 bit integer. |
| i3 | A 32 bit integer. |
| r4 | An 8 byte real number. |
| r8 | A 16 byte real number. |
| ui1 | An 8 bit unsigned integer. |
| ui2 | A 16 bit unsigned integer. |
| ui4 | A 32 bit unsigned integer. |
| uri | A Universal Resource Indicator |
| uuid | A get of hex digits representing a universal identifier (e.g. GUID) |
Apart from this list, W3C also allows a set of primitive data types: string, entity, entities, enumeration, id, idref, idrefs, nmtoken, nmtokens, and notation.
IE Data Islands and Binding
We have already seen a few examples of XML data islands and how to bind HTML elements to them in the previous tutorials. Let us take a look at it once again, since we’re going to be learning about data binding in detail today. We can create a data island with <XML> tag in an HTML page.
<xml ID= “diData” SRC = “BookAuthors.xml”></xml>
This references an external XML file as the source of the data. Alternatively, We can even embed the XML data within a <XML> tag.
<XML id=“diData” <BookAuthors>
<Author>
<au_id>1001</au_id>
<au_lname> Gates </au_name>
<au_fname> Bill </au_name>
</Author>
</BookAuthors>
</XML>
There are two forms of data binding. The first option is to bind single element, like so:
<INPUT dataFld=“emp_id”
dataSrc=“#diData” type=“TEXT”> </INPUT>
This binds a single element to a field in the data source. An alternative method would be to use table for binding. Here we bind a table to the data source, and then elements in the table to the data field:
<TABLE dataSrc=“#diData”>
<TBODY>
<TR>
<TD><INPUT dataFld=“emp_id” type=“TEXT”></INPUT></TD>
<TD><INPUT dataFld=“emp_fname”
type=“TEXT”></INPUT></TD></TR></TBODY></TABLE>
The Table would give the following result when DataBinding.html is loaded in IE.
The examples we’ve looked at so far have been quite simple, but you’ll find a problem if you try to bind a set of XML data generated by ADO. The reason is that IE doesn’t recognize schemas as a definition of the data. IE looks at it as follows:
<XML>
<s:Schema data>
…..Your schema data goes here
</s:Schema data>
<rs:data>
<z:row……/>
<z:row……/>
</rs:data>
</XML>
So, IE sees two sets of data: the Schema and the actual data. We have an additional problem to handle because we saw that data we want is held within the row. In short our data is two levels deep, which means extra work on our part. The solution is to use two levels of binding. We will see the sample DataBinding.html for the present scenario.
<TABLE dataFld=“rs:data” id=“tblData” dataSrc=“#diData”>
<TBODY>
<TR>
<TD>
<TABLE dataFld=“z:row” id=“tblData” dataSrc=“#diData” border=“1” <TR>
<TBODY>
<TR>
<TD><SPAN dataFld=“emp_id”></SPAN></TD>
<TD><SPAN dataFld=“emp_fname”></SPAN></TD>
Here, we bind the outer table to the elements containing all of the data. In the second step, we have an inner table bound to the row of data. Finally the fields are bound, just as before.
In the next part of the tutorial we will take a plunge into multiple levels of hierarchy, such as from a data shaped recordset, then we will add more levels to the binding. I hope that today’s section left you confident about integrating XML data with ADO and IE. Remember, experimentation is your friend. If you feel you need more practice, write up some code of your own! I think you’ll begin to enjoy this once you begin to really understand it.