Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Access Hierarchical XML Data with the XML OLE DB Simple Provider


View products that this article applies to.

This article was previously published under Q271772

↑ Back to the top


Summary

The OLE DB Simple provider for XML (XML OSP) can be used to load the hierarchical data that is in an XML document into a read-only ADO recordset. The data can then be read and accessed by using the standard methods of the ADO Recordset object. The XML OSP can be used to provide a different method for working with data that is contained in XML documents.

This article has step-by-step instructions to set up a Visual Basic 6.0 sample that demonstrate how to access hierarchical data that is contained in an XML document with the XML OSP.

↑ Back to the top


More information

Run the following steps to set up and test a Visual Basic sample that demonstrates how to use the OLE DB Simple provider for XML:
  1. Open an empty file in Microsoft Notepad. Copy and paste the following XML into the file and save it as portfolio.xml:
    <portfolio>
        <stock>
            <symbol>MSFT</symbol>
            <price>$66.00</price>
            <info>
                <companyname>Microsoft Corporation</companyname>
                <website>http://www.microsoft.com</website>
            </info>
        </stock>
        <stock>
            <symbol>AAPL</symbol>
            <price>$110.00</price>
            <info>
                <companyname>Apple Computer, Inc.</companyname>
                <website>http://www.apple.com</website>
            </info>
        </stock>
        <stock>
            <symbol>DELL</symbol>
            <price>$50.00</price>
            <info>
                <companyname>Dell Corporation</companyname>
                <website>http://www.dell.com</website>
            </info>
        </stock>
        <stock>
            <symbol>INTC</symbol>
            <price>$136.00</price>
            <info>
                <companyname>Intel Corporation</companyname>
                <website>http://www.intel.com</website>
            </info>
        </stock>
    </portfolio>
    					
  2. Open a Standard EXE project in Visual Basic. Form1 is created by default.
  3. Add a Project reference to the Microsoft ActiveX Data Objects 2.6 library.
  4. Add a CommandButton to Form1.
  5. Copy and paste the following code in the form module and specify the path to portfolio.xml in the adoRS.Open statement:
    Private Sub Command1_Click()
        Dim adoRS As ADODB.Recordset
        Set adoRS = New ADODB.Recordset
        
        ' Set up the Connection
        adoRS.ActiveConnection = "Provider=MSDAOSP; Data Source=MSXML2.DSOControl.2.6;"
        
        ' Open the XML source
        adoRS.Open  "<path to portfolio.xml>"
        On Error GoTo RecError
        printtbl adoRS, 0
        
        GoTo Bye
        
    RecError:
        Debug.Print Err.Number & ": " & Err.Description
        If adoRS.State = adStateOpen Then
            For Each Col In adoRS.Fields
                Debug.Print Col.Name & ": " & Col.Status  '  Error Status
            Next Col
        End If
        
    Bye:
        If adoRS.State = adStateOpen Then
            adoRS.Close
        End If
        Set adoRS = Nothing
    End Sub
    
    ' Function to recursively retrieve the data
    Sub printtbl(rs, indent)
        On Error Resume Next
        
        Dim rsChild As ADODB.Recordset
        Dim Col As ADODB.Field
        
        While rs.EOF <> True
            For Each Col In rs.Fields
            If Col.Name <> "$Text" Then   ' $Text to be ignored
                If Col.Type <> adChapter Then
                    ' Output the non-chaptered column
                    Debug.Print Space(indent) & Col.Name & ": " & Col.Value,
                Else
                    Debug.Print
                    ' Retrieve the Child recordset
                    Set rsChild = Col.Value
                    rsChild.MoveFirst
                    If Err Then MsgBox Error
                    printtbl rsChild, indent + 4
                    rsChild.Close
                    Set rsChild = Nothing
                End If
            End If
            Next
            Debug.Print
            rs.MoveNext
        Wend
        
    End Sub
    					
  6. Save the project and run it. Click the CommandButton on Form1 to run the Visual Basic ActiveX Data Objects (ADO) code that uses the XML OSP to load the data that is in portfolio.xml into an ADO recordset object. The code then calls the printtbl subprocedure and passes the loaded ADO recordset as an input parameter to it. The code in the printtbl subprocedure loops through the recordset and prints its contents to the debug window. Press the CTRL-G keys to view the debug window if it is not visible in the Visual Basic integrated development environment (IDE).

↑ Back to the top


Keywords: KB271772, kbmsxmlnosweep, kbhowto

↑ Back to the top

Article Info
Article ID : 271772
Revision : 4
Created on : 6/29/2004
Published on : 6/29/2004
Exists online : False
Views : 334