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

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
					

<?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>
					

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