XMLOLEDB Provider

I stumbled upon a download of an XMLOLEDB Provider. Unlike MsPersist, which utilizes ADO Recordsets, this Provider will permit SQL query for standard/tagged XML files. So you can open an ADODB connection to [say] books.xml and issue

RS=DB.Execute("SELECT author FROM books WHERE published='1997'")
While ! RS.eof()
 ...
Endwhile

the downside is there is no documentation with the dll.

The attached script uses the XML file I uploaded last night (test.xml). As there is no documentation for the Provider, the users knowledge of XML and SQL should at least be mediocre. I also think that XML 4.0 needs to be installed (but that version is free and has been around for a while). I registered the dll from \windows\system on my Win98 laptop.

I tested and provided 3 types of SQL statements. I am sure some limits and gotchas will be discovered and I trust if you find the Provider useful you will detail those on this board.

;// Winbatch - basic example of using XMLOLEDB Provider
;//            XMLOLEDB.DLL must be registered!
;//
;// NOTE: the provider adds a rowid Field
;//
;// This script assumes knowledge of well-formed XML
;// if you intend to test with your own data
;//
;// stan littlefield
;/////////////////////////////////////////////////////////

;// test for Provider Registration/test file
If ! RegExistKey(@REGCLASSES,"XmlOleDb.XML")
   Message("Cannot Continue",StrCat("XMLOLEDB Provider",@CRLF,"is not registered.") )
   Exit
EndIf
cFile = StrCat( DirGet(),"test.xml")
If ! FileExist( cFile ) Then Exit


BoxOpen("XMLOLEDB Provider","Loading... %cFile%")
cConn = "Provider=XmlOleDb.XML; Location=%cFile%"
DB    = ObjectOpen("ADODB.Connection")
RS    = ObjectOpen("ADODB.Recordset")
DB.CursorLocation = 3 ;adUseClient - important!!!

DB.Open(cConn)

;////  Issuing SQL Statements to the Provider ///
; try these one at a time, just comment/uncomment cSQL=
; then test your own
cSQL  = "SELECT * FROM Person"

; note: how the WHERE clause must reference the Address tag
;cSQL = "SELECT * FROM Person, Address WHERE state='NC'

; this next one is tricky, but it did work for me
; it is the same as the SQL above ( WHERE state='CA' )
; but using hints is particularly useful for complex
; XML, i.e. navigating to grand-children or parents
; - note use of delimiters
;cSQL = `SELECT * FROM Person, Address hint("./Address[@state='CA']")`


BoxText("Issuing SQL")
RS    = DB.Execute( cSQL )
BoxText("Parsing Fields")
flds  = RS.Fields
n     = flds.Count

;// Enumerate Columns using standard ADO - Sweeeeet....
Message("XMLOLEDB","About to Display Column Names")
For i = 0 To (n-1)
   fld = RS.Fields(i)
   Message( StrCat("Field ",i+1), fld.Name )
Next

;// Display Values with ADO
Message("XMLOLEDB","About to Display Field Values")
fld1  = RS.Fields("familyname")
fld2  = RS.Fields("Description")
RS.MoveFirst()
If ! RS.eof()
   While ! RS.eof()
      Message(fld1.Value,fld2.Value)
      RS.MoveNext()
   EndWhile
EndIf

RS.Close()
ObjectClose(RS)
DB.Close()
ObjectClose(DB)
BoxShut()
Exit
;///////////////////////////////////////////////

Test.XML

<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>
  <Person familyName="Littlefield" firstName="Stan">
    <Address street="Under The Bridge" number="9999" state="NC"/>
    <JobInfo jobDescription="Unemployed"/>
    <Description>Unemployed</Description>
  </Person>
  <Person familyName="Fink" firstName="Mike">
    <Address street="Meadow Lane" number="1000" state="NC"/>
    <JobInfo jobDescription="CEO"/>
    <Description>CEO</Description>
  </Person>
</AddressBook>

Article ID:   W17351

File Created: 2007:07:03:14:29:34

Last Updated: 2007:07:03:14:29:34

Database Search

TechHome

XMLOLEDB Provider