XML Integration with ADO and Internet Explorer 5

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. 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...

Contributed by Gayathri Gokul
Rating:

/ 19
November 17, 2003

Rate this Article:
MEH MEH++

SEARCH ASP FREE

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.