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

Article translations Article translations
Article ID: 271619 - View products that this article applies to.
This article was previously published under Q271619
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all
For a Microsoft Visual Basic .NET version of this article, see 301111.

SUMMARY

This article demonstrates how to programmatically retrieve an XML data stream from a SQL Server 2000 mapping schema by using an XPath query. The scope operates in a client/server (2-tier) model.

This sample also allows you to test XPath queries against the mapping schema. For each query, the text box of the form displays the XML; the elements of the XML data stream are broken onto separate lines for viewing purposes.

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']
    					

REFERENCES

For SQL Server 2000, please see SQL Server Books Online.

For XML, see the MSDN page on the following Microsoft Web site:
http://msdn.microsoft.com/xml

Properties

Article ID: 271619 - Last Review: February 28, 2014 - Revision: 4.6
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft XML Parser 2.6
  • Microsoft XML Parser 3.0
  • Microsoft XML Core Services 4.0
Keywords: 
kbnosurvey kbarchive kbhowto KB271619

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com