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

MORE INFORMATION

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).