Saving the ADO Recordset to XML in Memory

If you are working with ADO 2.5 or higher, you can take advantage of the Stream object to save your ADO recordset in XML format directly to memory, instead of a disk file. You can then process your recordset as needed. The contents of the Stream object can be printed out using the Stream object's ReadText method. Saving the recordset to memory is much faster and more scalable than saving to a disk file, especially when all you need to do is manipulate the recordset and you don't need a physical XML file to send to someone.

The example procedure that follows demonstrates how to use the Stream object. The procedure retrieves the records from the Shippers table; however, instead of creating an XML disk file, it saves the recordset to memory. Once the data is in the Stream object, you can close the recordset and work with the data as needed; for instance, remove one or more document nodes from the final output presented to the user. You can also save the contents of the Stream object to a local file with the Stream object's SaveToFile method. This method takes two parameters—a String value with the fully-qualified name of the file to which the contents of the Stream should be saved and a constant that specifies whether a file should be created (adSaveCreateNotExist) or overwritten (adSaveCreateOver-Write) when saving from a Stream object.

The following exercise demonstrates how to create in memory XML files with the ADO Stream object.

1. In a new module, enter the code of the SaveToStream procedure shown below.

2. Run the procedure in a step mode by pressing F8. Make sure the Immediate window is open so you can see at once the results of various Debug.Print statements that the example procedure contains.

Sub SaveToStream()

Dim conn As ADODB.Connection

Dim rst As ADODB.Recordset

Dim myStream As ADODB.Stream

Dim xmlDoc As MSXML2.DOMDocument30

Dim xslDoc As MSXML2.DOMDocument30

Dim myNode As MSXML2.IXMLDOMNode

Dim parentNode As MSXML2.IXMLDOMNode

Dim objFSO As Object

Dim objTextFile As Object

Dim strContents As String

Dim strFileN As String

On Error GoTo ErrorHandler

' declare constant used as database connection string Const strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Program Files\Microsoft Office\" _ & "Office\Samples\Northwind.mdb"

' open a connection to the database Set conn = New ADODB.Connection conn.Open strConn

' open the Products table

Set rst = New ADODB.Recordset rst.Open "Shippers", conn, adOpenStatic, adLockOptimistic

' create a Stream object

Set myStream = New ADODB.Stream

' save the recordset to XML in memory rst.Save myStream, adPersistXML

' close the recordset rst.Close

Set rst = Nothing

' save the contents of the Stream object to a variable strContents = myStream.ReadText Debug.Print strContents

' use SaveToFile method to copy the contents ' of a Stream object to a local file strFileN = "C:\xmlFromStream.xml" myStream.SaveToFile strFileN, adSaveCreateNotExist

' Load the XML content to the DOM object Set xmlDoc = New MSXML2.DOMDocument30 xmlDoc.loadXML strContents Debug.Print xmlDoc.XML

' manipulate the contents of the Stream object here ' for example, the following code removes a node from final output Set myNode = xmlDoc.selectSingleNode( _

"//z:row[@CompanyName='United Package']") Set parentNode = xmlDoc.selectSingleNode("//rs:data") parentNode.removeChild myNode Debug.Print xmlDoc.XML

' load the XSL stylesheet from disk Set xslDoc = New MSXML2.DOMDocument30 xslDoc.async = False xslDoc.Load "C:\AttribToHTML.xsl"

' transform XML

strHTML = xmlDoc.transformNode(xslDoc) Debug.Print strHTML

' save the strHTML to a File strFileN = "C:\XmlFromStream_Transformed.htm"

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objTextFile = objFSO.CreateTextFile(strFileN, True)

objTextFile.Write strHTML


ExitHere: ' Cleanup

Set xmlDoc = Nothing

Set xslDoc = Nothing Set myStream = Nothing Set conn = Nothing Exit Sub ErrorHandler:

If Err.Number = 3004 Then myStream.SaveToFile strFileN, adSaveCreateOverWrite Resume Next


MsgBox Err.Number & ": " & Err.Description GoTo ExitHere End If End Sub

0 0


Post a comment