Integration with DOM

Just as an ADO Recordset can be saved directly into the ASP Response object, it can also be saved directly into the XML DOM Document object. This avoids having to save the Recordset into an intermediate stream and then loading that stream into the DOM. The following program shows how DOM can be used to completely modify XML generated by the ADO Save method:

Private Sub Command1_Click()
    Dim rsXML As New ADODB.Recordset
    Dim sSQL As String, sConn As String
    
    sSQL = "SELECT customerid, companyname, contactname FROM customers"
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Common Files\System\msadc\samples\NWind.mdb"
    rsXML.CursorLocation = adUseClient
    rsXML.Open sSQL, sConn, adOpenStatic

    Dim xDOM As New MSXML.DOMDocument
    Dim x2DOM As New MSXML.DOMDocument
    Dim dataNodeList As IXMLDOMNodeList
    Dim dataNode, rowNode, newNode, newRoot, newNode2 As IXMLDOMNode
    
    rsXML.Save "C:\temp\rsDOM.xml", adPersistXML
    ' Save Recordset directly into a DOM tree.
    rsXML.Save xDOM, adPersistXML
    Set dataNodeList = xDOM.getElementsByTagName("rs:data")
    Set dataNode = dataNodeList.Item(0)
    
    ' Create a new Node under new DOM.
    Set newRoot = x2DOM.createNode(NODE_ELEMENT, "recordset", "")
    ' Append root node.
    Set newRoot = x2DOM.appendChild(newRoot)
    
    i = 0
    ' Loop through all z:row nodes.
    While i < dataNode.childNodes.length
        ' Get to the z:row node.
        Set rowNode = dataNode.childNodes.Item(i)

        Set newNode = x2DOM.createNode(NODE_ELEMENT, "row", "")
        ' Append row node under new root.
        Set newNode = newRoot.appendChild(newNode)      
        
        j = 0
        ' Loop through all attributes of z:row.
        While j < rowNode.Attributes.length
        
            ' Create new element for column name.
            Set newNode2 = x2DOM.createNode(NODE_ELEMENT, rowNode.Attributes.Item(j).baseName, "") 
            ' Assign column value to new node value.
            newNode2.Text = rowNode.Attributes.Item(j).Text
            
            ' Append new column node under new node.
            Set newNode2 = newNode.appendChild(newNode2)
            
            j = j + 1
        Wend
        
        i = i + 1
    Wend
    
    x2DOM.Save "C:\temp\rsCustData.xml"
End Sub