Persisting records in XML format

Applies to: Access 2013, Office 2013

Like ADTG format, Recordset persistence in XML format is implemented with the Microsoft OLE DB Persistence Provider. This provider generates a forward-only, read-only rowset from a saved XML file or stream that contains the schema information generated by ADO. Similarly, it can take an ADO Recordset, generate XML, and save it to a file or any object that implements the COM IStream interface. (In fact, a file is just another example of an object that supports IStream.) For versions 2.5 and later, ADO relies on the Microsoft XML Parser (MSXML) to load the XML into the Recordset; therefore msxml.dll is required. For version 2.5, MSXML shipped with Internet Explorer 5. For version 2.6, MSXML shipped with SQL Server 2000.

Note

Some limitations apply when saving hierarchical Recordsets (data shapes) to XML format. You cannot save to XML if the hierarchical Recordset contains pending updates, and you cannot save a parameterized hierarchical Recordset. For more information, see Hierarchical Recordsets in XML.

The easiest way to persist data into XML and load it back again through ADO is with the Save and Open methods, respectively. The following ADO code example demonstrates saving the data in the Titles table to a file named titles.sav.

 
Dim rs as new Recordset 
Dim rs2 as new Recordset 
Dim c as new Connection 
Dim s as new Stream 
 
' Query the Titles table. 
c.Open "provider='sqloledb';data source='mydb';initial catalog='pubs';Integrated Security='SSPI'" 
rs.cursorlocation = adUseClient 
rs.Open "select * from titles", c, adOpenStatic 
 
' Save to the file in the XML format. Note that if you don't specify 
' adPersistXML, a binary format (ADTG) will be used by default. 
rs.Save "titles.sav", adPersistXML 
 
' Save the Recordset into the ADO Stream object. 
rs.save s, adPersistXML 
rs.Close 
c.Close 
 
set rs = nothing 
 
Reopen the file. 
rs.Open "titles.sav",,,,adCmdFile 
'Open the Stream back into a Recordset. 
rs2.open s 

ADO always persists the entire Recordset object. If you wish to only persist a subset of rows of the Recordset object, use the Filter method to narrow down the rows or change your selection clause. However, you must open a Recordset object with a client-side cursor (CursorLocation = adUseClient) to use the Filter method for saving a subset of rows. For example, to retrieve titles that start with the letter "b," you can apply a filter to an open Recordset object:

 
rs.Filter "title_id like 'B*'" 
rs.Save "btitles.sav", adPersistXML 

ADO always uses the Client Cursor Engine rowset to produce a scrollable, bookmarkable Recordset object on top of the forward-only data generated by the Persistence Provider.

This section includes the following topics: