OLE DB Provider for XML

• Use the Simple Provider or SQLXML from Microsoft. For some reason this requires a MS SQL Server.
• Purchase an XML ODBC Driver from Merant, which comes with a price-tag of $2,500 for a server license (who are they kidding?)

An OLE DB Provider is a database driver, which uses Microsoft COM technology. The more famous layer, ADO, is built on top of OLE DB and leverages an "easy access to databases for Visual Basic programmers"-layer.
OLE DB is not solely targeted for SQL databases, but rather tries to encompass all kinds of data sources. For this reason (and because it uses a lot of COM stuff) it is a rather complex task to develop them.

The XML Provider

SQL conformance

SELECT Name, Phone FROM Employees WHERE Phone = '1234'

The Provider deduces the table relations itself, so no additional information is required to describe how SQL relations are structured in the document (no additional schema information required).

It is possible to use AND and OR logical connectives to refine searches in the WHERE clause, as well as most arithmethic operators. It basically translates what you write into an XPath query so that should give you an idea of the limitations of the query processor.

The Provider is a read-only provider. It does not support INSERT, UPDATE or DELETE data manipulation.
There is no support for built-in functions, grouping or calculated column values. Nor does it support table and column aliases (they can be specified but are ignored). No aggregated functions are implemented.

Tables

The XML Provider looks for repeating nodes at the same hierarchy level and treats them as tables. Meaning, all nodes that contain child nodes are potential tables.
It determines the columns available in the table by looking at the child nodes of the first entry it finds.

Here is a sample:

<AddressBook>
  <Person familyName="Gates" firstName="Bob">
    <Address street="Pine Rd." number="1239" state="CA"/>
    <JobInfo jobDescription="Manager"/>
    <Description>Family man</Description>
  </Person>
  <Person familyName="Brown" firstName="Robert">
    <Address street="118 St." number="344" state="NY"/>
    <JobInfo jobDescription="Group Leader"/>
    <Description>Hacker</Description>
  </Person>
  <Person familyName="DePaiva" firstName="Kassie">
    <Address street="Pine Rd." number="1234" state="CA"/>
    <JobInfo jobDescription="Actor"/>
    <Description>Actor</Description>
  </Person>
</AddressBook>

There are several other tables, which can be queried in this sample. They are "AddressBook", "Address" and "JobInfo".

Table Hints

SELECT <fields> FROM <table> hint("<XPath expression>")

SELECT * FROM 
  Person, Address hint("./Address[@state='CA']")

SELECT * FROM Person, Address WHERE state='CA'

Columns

If your query contains multiple relations (tables) you may reference columns in different tables by prefixing with the table-name. This is standard SQL stuff.

SELECT Address.street FROM Person, Address
  WHERE Person.firstName='Bob' AND Address.state='CA'

Special Columns

Data types

String columns are truncated to 120 characters! This is configurable through the Providers properties as described later.

Joining tables

The provider supports two types of joins. The first is a little non-standard.

SELECT firstName, state FROM Person, Address

The second join syntax looks more like the standard SQL syntax.

SELECT firstName FROM Person, Address WHERE
  Person.id = Address.personId

The Provider always starts from the left-most table (as defined in the FROM clause) and works its way through the remaining tables. It is usually a good idea to start searches with the top-most XML nodes, and adding child node tables as they appear in the XML document.

Because of the nesting of table access when you don't provide a unique ID for the join, you may need a table hint when you join more than 2 tables:

SELECT firstName, state, jobDescription FROM Person, Address,
  JobInfo hint("../JobInfo")

Sorting

Reserved Keyword

SELECT UPDATE DELETE 
FROM WHERE ORDER BY 
SET AND OR 

Using the Provider

Configuration properties

Accessing the data

var adUseClient = 3;
var oDb = new ActiveXObject("ADODB.Connection");
oDb.Open("Provider=XmlOleDb.XML; Location=C:\\xml\\Test.xml");
oDb.CursorLocation = adUseClient;
var sSQL = "SELECT * FROM Person";
var oRec = oDb.Execute(sSQL);
while( !oRec.EOF ) {
   WScript.Echo( oRec("firstName") );
   oRec.MoveNext();
}
oRec.Close();
oDb.Close();

Accessing the Provider from C++ is similar, and the standard ATL Consumer templates can be used. Note that the Provider's ProgID is XmlOleDb.XML.

The provider also works with the Data Bound controls in Visual Basic 6. You need to use the ADO Data Source as the data source control.

A final note...

And please don't ask me to publish the source code for this component. The source code for the XML OLEDB Provider will not be released.

Installation Requirements

Installation Guide

• Extract and copy the files to a local folder.
• Execute the "install.js" script.

Download Files  

OLE DB Provider for XML (170 Kb)