Executing XPath Queries (SQLXMLOLEDB Provider)

This example illustrates the use of the following SQLXMLOLEDB Provider-specific properties:

• ClientSideXML

• Base Path

• Mapping Schema

In this sample ADO application, an XPath query (root) is specified against an XSD mapping schema (MySchema.xml). The schema has a <Contacts> element with ContactID, FirstName, and LastName attributes. In the schema, default mapping takes place: an element name maps to the table with the same name, and attributes of simple type map to the columns with the same names.

<xsd:schema xmlns:xsd='http://www.w3.org/2001/XMLSchema'  
   xmlns:sql='urn:schemas-microsoft-com:mapping-schema'>  
 <xsd:element name= 'root' sql:is-constant='1'>   
    <xsd:complexType>  
       <xsd:sequence>  
         <xsd:element ref = 'Contacts'/>  
       </xsd:sequence>  
    </xsd:complexType>  
  </xsd:element>  
  <xsd:element name='Contacts' sql:relation='Person.Contact'>   
     <xsd:complexType>  
          <xsd:attribute name='ContactID' type='xsd:integer' />  
          <xsd:attribute name='FirstName' type='xsd:string'/>   
          <xsd:attribute name='LastName' type='xsd:string' />   
     </xsd:complexType>  
   </xsd:element>  
</xsd:schema>  

The Mapping Schema property provides the mapping schema against which the XPath query is executed. The mapping schema can be an XSD or XDR schema. The Base Path property provides the file path to the mapping schema.

The ClientSideXML property is set to True. Therefore, the XML document is generated on the client.

In the application, an XPath query is specified directly. Therefore, the XPath dialect {ec2a4293-e898-11d2-b1b7-00c04f680c56} must be included.

Note

In the code, you must provide the name of the instance of SQL Server in the connection string. Also, this example specifies the use of the SQL Server Native Client (SQLNCLI11) for the data provider which requires additional network client software to be installed. For more information, see System Requirements for SQL Server Native Client.

Option Explicit  
Sub main()  
Dim oTestStream As New ADODB.Stream  
Dim oTestConnection As New ADODB.Connection  
Dim oTestCommand As New ADODB.Command  
  
oTestConnection.Open "provider=SQLXMLOLEDB.4.0;data provider=SQLNCLI11;data source=SqlServerName;initial catalog=AdventureWorks;Integrated Security= SSPI;"  
  
oTestCommand.ActiveConnection = oTestConnection  
oTestCommand.Properties("ClientSideXML") = True  
  
oTestCommand.CommandText = "root"  
oTestStream.Open  
oTestCommand.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"  
oTestCommand.Properties("Output Stream").Value = oTestStream  
oTestCommand.Properties("Base Path").Value = "c:\Schemas\SQLXML4\XPathDirect\"  
oTestCommand.Properties("Mapping Schema").Value = "mySchema.xml"  
oTestCommand.Properties("Output Encoding") = "utf-8"  
oTestCommand.Execute , , adExecuteStream  
oTestStream.Position = 0  
oTestStream.Charset = "utf-8"  
Debug.Print oTestStream.ReadText(adReadAll)  
  
End Sub  
Sub Form_Load()  
 main  
End Sub