Generating XML from ADO Recordsets

Generating XML from ADO Recordsets

n an ideal world, the process of communicating between XML and the database world would be completely seamless and transparent?you point an XML DOM at a database, say Update or Request, and you’re done. In this all-too-messy real world where the “standards” change from day to day and no two database companies would dare agree on a common format for fear that it may give their competitor a marginal advantage, this utopian vision is not quite here yet.

However, that’s not to say that there hasn’t been progress in the XML to RDBMS (relational database management system) front. At the XML One Fall ’99 conference in Santa Clara, Oracle debuted their superb Oracle 8i update, which provided a number of XML features. Microsoft’s SQL Server 7 also incorporated a number of basic XML features into their Active Data Objects (ADO) 2.5 engine, which also provides some fixes for basic limitations in the ADO 2.1 provider.

The XML format that the Microsoft ADO 2.5 (and ADO 2.1) engine produces is written in Microsoft XML-Data Schema format, which is also known as theXML Reduced Data Schema, or simply Reduced Data. The XML Reduced Data Schema works by specifying the datatypes and similar characteristics of the schema (default values, primary key information, and so forth) from the database and placing this information into the first half of the document. A similar process then extracts the data to place in XML row nodes. By separating these, you can cut down on the amount of spurious information that the XML document contains while still being able to get relevant datatype information.

For one recent project, I needed to get information about role-playing characters for an online Web game. I stored the characters table in an Access database (characters.mdb), then used ADO 2.5 to retrieve a general recordset query (see Listing 1).

The conversion from database to XML is done with the function rs.save(). Save basically takes the recordset and converts it into a stream format. If the first argument given is a string containing a URL, then the function save() outputs the data into its intrinsic binary format. However, by passing the adPersistXML flag, the stream is converted into an XML stream instead.

In ADO 2.1, you were forced to output the stream to a file, which proved a significant performance hit. The stream had to be converted into a Unicode formatted text string, spooled out to the hard drive through a standard file interface, then if you needed the resultant XML, the file had to be reloaded and reparsed back into XML stream. ADO 2.5 lets you write the result directly out to an XML DOM Document, bypassing both conversion and parsing, and making for much smoother code. You can see the output for the data provided in the characters.mdb file in Listing 2.

A Trip Through Namespace
There are four explicit namespaces defined in the Microsoft XML Reduced Data Schema:

  • The XML Reduced Data Schema namespace “s”, which contains the specific information about the structure of the internal XML format used by each rowset. (xmlns:s=”uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882″ )
  • The Data Type Specification namespace “dt”, which points to Microsoft’s internal data typing mechanism.(xmlns:dt=”uuid:C2F41010-65B3-11d1-A29F-00AA00C14882″ )
  • The Rowset namespace “rs”, which contains information about the database implementation for each row. (xmlns:rs=”urn:schemas-microsoft-com:rowset” )
  • The RowsetSchema namespace “z”, which contains the actual data from the database. This utilizes the schema defined by the “s” namespace. (xmlns:z=”#RowsetSchema”)

This set of namespaces actually provides some interesting usage information. Both the Microsoft XML Reduced Data Schema namespace and the Data Type Specification namespace actually point to GUIDs (globally unique identifiers), which uniquely identify them to the system. However, these GUIDs (actually UUIDs?universally unique IDs?which is the preferred term) don’t explicitly point to any COM objects, but instead are used as identifying tags by the XML parser to map internally to the proper objects. By doing this, the parser treats the namespace UUID as a handle, or a pointer to a pointer, making it easier to upgrade the respective parsers in the future.

The Rowset namespace, on the other hand, points to a more traditional flavor of namespace: a URN (Uniform Resource Name) that the XML parser identifies with the Reduced Data rowset. The URN uses notation that may be less familiar if you are used to working with the URL notation of the Web, but urn:schemas-microsoft-com:rowset is more or less the same as the URL http://schemas.microsoft.com/rowset.

The final namespace, RowsetSchema or “z” namespace, retrieves the schema identified by the ID “RowsetSchema” earlier in the document. This schema is local, created on the fly by the ADO package. Because a typical database recordset will change based upon the SQL source string, this schema will change as well. Thus, you could control the way that the schema is generated by being more specific with your query. For example, this code will output just these four elements into the schema, rather than the entire table:

Rs.open "SELECT characterName, vocation, species, gender FROM Characters" 

The Microsoft XML Reduced Data Schema format intrinsically associates datatypes to each of the attributes. Thus, if you make a request using the getAttribute method or nodeTypedValue property of the XML DOM, the value returned will be of the correct datatype. For example, to retrieve the strength attribute of the Elf Sheinara, you’d use these XML calls:

Set strengthNode=rdataXML.selectSingleNode(_       "//z:row[@characterName='Sheinara']/@Strength")Strength=strengthNode.nodeTypedValue' or Set characterNode=rdataXML.selectSingleNode(_       "//z:row[@characterName='Sheinara']")Strength=characterNode.getAttribute("Strength")

In either case, the value is returned as 0.96, which is the four digit approximation (with trailing zeroes not explicitly displayed) of the internal format “0.95999999999999996”. Put another way, these two elements return a floating point number, not a string.

Converting to an Element Schema
Still, the z:row format has some serious limitations to it. In general, while attributes process a little faster than elements (they have their own specialized interface, and as such can be optimized for performance, whereas the more general elements cannot), they may not necessarily be in the most preferred format for your own use. For example, suppose that you wanted to turn the attributes into a more traditional element-based XML structure that looks like Listing 3.

You can perform the conversion in a specific case (for instance, with known container and object names, such as characters and character) with an XSL script as shown in Listing 4. One of the primary problems with such a script is that you would need to explicitly change the source code for the script every time you wanted to run it to take into account different object or group names. While you can parameterize the output, this undertaking is not trivial.

However, this is one of those places where the DOM is actually the more efficient way of handling the problem. The convertToElementTree function actually emulates the functionality of the XSL script, but because you can pass in the relevant group and object names as parameters into a function, it is both more concise and easier to manage (see Listing 5). It takes as an argument a Reduced Data XML structure, the name of the group, and the name of each object in the group, then returns a DOM object in the element-only format.

To generate the sample output shown in Listing 3, you would need to call the convertToElementTree function as follows:

Dim charactersDoc as DOMDocumentSet charactersDoc=ConvertToElementTree(_        rdataXML,"characters","character")

Towards a Schema Standard?
I should note that the beta version of Windows SQL Server 7.5 actually generates such a format by default (although you may need to change the containing elements), at least when called from a Web address. Currently, only Microsoft and its partners support the XML Reduced Data Schema format, and in all likelihood it will end up being principally an internal data format, especially when the W3C XML Schema becomes a formal recommendation.

Microsoft originally designed the XML Reduced Data Schema as a proposal for identifying and handling data in the XML space, and it was an instrumental document for creating the W3C XML Schema. However, because the latter schema is an open standard, it has been tweaked and changed to better reflect the needs and requirements of other database vendors such as Oracle or IBM. The Schema standard proposal itself is divided up into two “Working Draft” documents: The W3C XML Schema Structures document (www.w3.org/TR/xmlschema-1/) and the W3C XML Schema Data-type document (www.w3.org/TR/xmlschema-2/).

The first document specifies the Schema architecture, and is structurally similar to the Microsoft XML Reduced Data Schema discussed in this article?the primary differences have to do with the addition of archetypes to allow for the creation of custom datatypes. The second document, on the other hand, focuses on the datatypes themselves, including ways of creating and inheriting datatype specifications. While many of these are similar to the original Microsoft datatypes (such as datatypes for integers, real numbers, dates, and similar “primitive” types), the names or scoped ranges may have changed since the Microsoft XML Reduced Data Schema was first proposed.

The W3C Schema specifications are currently considered Working Drafts (and should be entering Last Call shortly), although they will likely become Proposed Recommendations to the XML canon by early in 2000. For those unfamiliar with the terminology involved, a Working Draft is a document that is currently in development, much like a legislative bill in the United States Congress. Last Call indicates that the participating members of the W3C can include minor amendments or clarifications to the document, but cannot substantively change the document. If it passes when the W3C members vote on the draft, it is considered a Proposed Recommendation and is immutable at that point; otherwise, it goes back to being a Working Draft.

Finally, the Proposed Recommendation becomes a formal Recommendation after a final vote, pending evidence that would prove that the standard is unworkable. A Recommendation is the closest thing to a law in the W3C. The document is considered stable and can be coded against, and any changes that need to be made to it would instead be considered part of the next version.

Recently, the XPath and XSL-Transform documents became formal Recommendations; the XHTML and XML DOM 2 specifications are now Proposed Recommendations; and MathML, XLink, and SVG are all in Last Call. Once the Schema specifications become Recommendations, then all of the critical pieces for XML development will be stable, which should have a profound impact on companies wishing to deploy XML solutions.

As a final note, the W3C also includes the Note designation for a document. This is a document that may present one member’s suggestions for implementing a certain technology, or may simply be a useful document for clarifying the needs and requirements for an XML technology. The Microsoft XML Reduced Data Schema proposal is just such a Note, and can be retrieved from the W3 site as well (www.w3.org/TR/1998/NOTE-XML-data-0105/). Check www.w3.org/TR for the status of all such Notes, Working Drafts, and Recommendations.

The XML Reduced Data Schema notation will probably be with Microsoft for a while, even after the W3C XML Schema documents become formal Recommendations. They form the underpinnings of much of the current data handling strategy for inter-Microsoft applications, and will probably have a big part to play in the upcoming Biztalk Server. With XSL or DOM code you can easily transform the data into other formats, and can even (with more effort than I can show in this article) modify the internal schema data representations into a variety of different schema configurations. This ability to transform both the data and the underlying schema of that data will figure large in your XML work in the future.

Click here to download the code and the sample Access database.

devx-admin

devx-admin

Share the Post:
Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023,

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed

Poland Energy Future

Westinghouse Builds Polish Power Plant

Westinghouse Electric Company and Bechtel have come together to establish a formal partnership in order to design and construct Poland’s inaugural nuclear power plant at the Lubiatowo-Kopalino site in Pomerania.

EV Labor Market

EV Industry Hurting For Skilled Labor

The United Auto Workers strike has highlighted the anticipated change towards a future dominated by electric vehicles (EVs), a shift which numerous people think will result in job losses. However,

Soaring EV Quotas

Soaring EV Quotas Spark Battle Against Time

Automakers are still expected to meet stringent electric vehicle (EV) sales quotas, despite the delayed ban on new petrol and diesel cars. Starting January 2023, more than one-fifth of automobiles

Affordable Electric Revolution

Tesla Rivals Make Bold Moves

Tesla, a name synonymous with EVs, has consistently been at the forefront of the automotive industry’s electric revolution. The products that Elon Musk has developed are at the forefront because

Sunsets' Technique

Inside the Climate Battle: Make Sunsets’ Technique

On February 12, 2023, Luke Iseman and Andrew Song from the solar geoengineering firm Make Sunsets showcased their technique for injecting sulfur dioxide (SO₂) into the stratosphere as a means

AI Adherence Prediction

AI Algorithm Predicts Treatment Adherence

Swoop, a prominent consumer health data company, has unveiled a cutting-edge algorithm capable of predicting adherence to treatment in people with Multiple Sclerosis (MS) and other health conditions. Utilizing artificial

Personalized UX

Here’s Why You Need to Use JavaScript and Cookies

In today’s increasingly digital world, websites often rely on JavaScript and cookies to provide users with a more seamless and personalized browsing experience. These key components allow websites to display

Geoengineering Methods

Scientists Dimming the Sun: It’s a Good Thing

Scientists at the University of Bern have been exploring geoengineering methods that could potentially slow down the melting of the West Antarctic ice sheet by reducing sunlight exposure. Among these

why startups succeed

The Top Reasons Why Startups Succeed

Everyone hears the stories. Apple was started in a garage. Musk slept in a rented office space while he was creating PayPal with his brother. Facebook was coded by a

Bold Evolution

Intel’s Bold Comeback

Intel, a leading figure in the semiconductor industry, has underperformed in the stock market over the past five years, with shares dropping by 4% as opposed to the 176% return

Semiconductor market

Semiconductor Slump: Rebound on the Horizon

In recent years, the semiconductor sector has faced a slump due to decreasing PC and smartphone sales, especially in 2022 and 2023. Nonetheless, as 2024 approaches, the industry seems to

Elevated Content Deals

Elevate Your Content Creation with Amazing Deals

The latest Tech Deals cater to creators of different levels and budgets, featuring a variety of computer accessories and tools designed specifically for content creation. Enhance your technological setup with

Learn Web Security

An Easy Way to Learn Web Security

The Web Security Academy has recently introduced new educational courses designed to offer a comprehensible and straightforward journey through the intricate realm of web security. These carefully designed learning courses

Military Drones Revolution

Military Drones: New Mobile Command Centers

The Air Force Special Operations Command (AFSOC) is currently working on a pioneering project that aims to transform MQ-9 Reaper drones into mobile command centers to better manage smaller unmanned

Tech Partnership

US and Vietnam: The Next Tech Leaders?

The US and Vietnam have entered into a series of multi-billion-dollar business deals, marking a significant leap forward in their cooperation in vital sectors like artificial intelligence (AI), semiconductors, and

Huge Savings

Score Massive Savings on Portable Gaming

This week in tech bargains, a well-known firm has considerably reduced the price of its portable gaming device, cutting costs by as much as 20 percent, which matches the lowest

Cloudfare Protection

Unbreakable: Cloudflare One Data Protection Suite

Recently, Cloudflare introduced its One Data Protection Suite, an extensive collection of sophisticated security tools designed to protect data in various environments, including web, private, and SaaS applications. The suite

Drone Revolution

Cool Drone Tech Unveiled at London Event

At the DSEI defense event in London, Israeli defense firms exhibited cutting-edge drone technology featuring vertical-takeoff-and-landing (VTOL) abilities while launching two innovative systems that have already been acquired by clients.

2D Semiconductor Revolution

Disrupting Electronics with 2D Semiconductors

The rapid development in electronic devices has created an increasing demand for advanced semiconductors. While silicon has traditionally been the go-to material for such applications, it suffers from certain limitations.

Cisco Growth

Cisco Cuts Jobs To Optimize Growth

Tech giant Cisco Systems Inc. recently unveiled plans to reduce its workforce in two Californian cities, with the goal of optimizing the company’s cost structure. The company has decided to

FAA Authorization

FAA Approves Drone Deliveries

In a significant development for the US drone industry, drone delivery company Zipline has gained Federal Aviation Administration (FAA) authorization, permitting them to operate drones beyond the visual line of

Mortgage Rate Challenges

Prop-Tech Firms Face Mortgage Rate Challenges

The surge in mortgage rates and a subsequent decrease in home buying have presented challenges for prop-tech firms like Divvy Homes, a rent-to-own start-up company. With a previous valuation of

Lighthouse Updates

Microsoft 365 Lighthouse: Powerful Updates

Microsoft has introduced a new update to Microsoft 365 Lighthouse, which includes support for alerts and notifications. This update is designed to give Managed Service Providers (MSPs) increased control and

Website Lock

Mysterious Website Blockage Sparks Concern

Recently, visitors of a well-known resource website encountered a message blocking their access, resulting in disappointment and frustration among its users. While the reason for this limitation remains uncertain, specialists