How To Retrieve XML Data by Using an XPath Query in a Visual Basic Client (271619)

MORE INFORMATION

1. Create a new Visual Basic application. Form1 is created by default.
2. On the Project menu, click References, and then set a reference to Microsoft ActiveX Data Objects.
3. Create two TextBox controls, that ae named and labeled txtResults and txtXPath, respectively. Size txtXPath to the width for your form and one line in height. To display the results, size txtResults as large as possible, and then set the Multi-line property to True.
4. Create two CommandButton controls, that are named and labeled cmdTestIt, and cmdExitProgram, respectively.
5. On the code window of Form1, paste the following code:

   Option Explicit
   
   Dim gCn As New ADODB.Connection
   
   '
   ' DBGUID values for the command object's dialect property
   '
   Const DBGUID_DEFAULT As String = "{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}"
   Const DBGUID_SQL As String = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
   Const DBGUID_MSSQLXML As String = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
   Const DBGUID_XPATH As String = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"
   
   Private Sub cmdExitProgram_Click()
       Unload Me
       End
   End Sub
   
   Private Sub cmdTestIt_Click()
   
       '   ADODB.Command used for the query
       Dim cmd As ADODB.Command
       '   ADODB.Stream used to set up the command to execute
       Dim strm As ADODB.Stream
   
       On Error GoTo trap
       '   create a new ADODB.Command
       Set cmd = New ADODB.Command
       '   establish connect for the command object to the database
       Set cmd.ActiveConnection = gCn
   
       '   create the ADODB.Stream for the results.
       Set strm = New ADODB.Stream
       '   open the result stream so it may receive the output from the execute
       strm.Open
       '   set the command type to an XPath query
       cmd.Dialect = DBGUID_XPATH
   
       '   set the file name for the mapping schema
       cmd.Properties("Mapping Schema") = App.Path & "\CustomerOrder.xdr"
       '   hook up the command to the result stream
       cmd.Properties("Output Stream") = strm
       '   trim off any additional space 
   
       txtXPath = Trim(txtXPath)
       If txtXPath = "" Then
           '   no search path default to customers. (CasE SeNsiTiVe)....
           txtXPath = "Customers"
       End If
   
       '   set the actual text for the XPath command
       cmd.CommandText = txtXPath
   
       '   execute the command stream
       cmd.Execute , , adExecuteStream
   
       '   reset the stream's position in order to read it 
       strm.Position = 0
   
       '   set the displayed results to the command's output
       txtResults = strm.ReadText
   
       '   clean up the output to make easier to read 
       txtResults = Replace(txtResults, "><", ">" & vbCrLf & "<")
   
      '   reset the stream's position in order to read it
       strm.Position = 0
   
       strm.Close
   
   GoTo cleanup
   
   trap:
   
       ' report errors 
       MsgBox "Error (" & Err.Number & ") -- " & Err.Description
   
   cleanup:
       '   clean up 
       Set strm = Nothing
       Set cmd = Nothing
   
       Exit Sub
   
   End Sub
   
   Private Sub Form_Load()
   
   On Error GoTo trap
       Set gCn = New ADODB.Connection
       gCn.ConnectionString = "PROVIDER=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;uid=sa;pwd="
       gCn.Open
       Exit Sub
   
   trap:
       MsgBox "Failed to connect to database.  Program Shutting down."
       Unload Me
       End
   
   End Sub
   					

6. Create the mapping schema against which the XPath queries. Either place the file into the project folder (app.path), or fully qualify the path to the file where the Mapping Schema property is set. Save as CustomerOrder.xdr.

   <?xml version="1.0" ?>
   
   <Schema xmlns="urn:schemas-microsoft-com:xml-data"
           xmlns:dt="urn:schemas-microsoft-com:datatypes"
           xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   
       <ElementType name="CustOrder" sql:relation="Orders">
           <AttributeType name = "CustomerID" />
           <AttributeType name = "OrderID" />
           <AttributeType name = "OrderDate" />
           <AttributeType name = "ShipCity" />
   
           <attribute type = "CustomerID"  sql:field="CustomerID" />
           <attribute type = "OrderID"     sql:field="OrderID" />
           <attribute type = "OrderDate"   sql:field="OrderDate" />
           <attribute type = "ShipCity"    sql:field="ShipCity" />
   
       </ElementType>
   
       <ElementType name="Customers" sql:relation="Customers">
   
           <AttributeType name = "CustomerID" />
           <AttributeType name = "CompanyName" />
           <AttributeType name = "ContactName" />
           
           <attribute type = "CustomerID"   sql:field="CustomerID" />
           <attribute type = "CompanyName"  sql:field="CompanyName" />
           <attribute type = "ContactName"  sql:field="ContactName" />
           
           <element type="CustOrder" >
               <sql:relationship key-relation="Customers"
                              key="CustomerID"
                              foreign-key="CustomerID"
                              foreign-relation="Orders"  />
           </element>
           
       </ElementType>
       
   </Schema>
   					

7. Save the mapping schema and run the sample. To test the query, you can enter the following XPath queries into the txtXPath textbox. (The default is set to Customers.)

       Customers
       Customers[@CustomerID]
       Customers[@CustomerID='ALFKI']
       Customers/CustOrder[@CustomerID='ALFKI']