执行 SQL 查询(SQLXMLOLEDB 访问接口)Executing SQL Queries (SQLXMLOLEDB Provider)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

该示例说明以下特定于 SQLXMLOLEDB 访问接口的属性的用法:This example illustrates the use of the following SQLXMLOLEDB Provider-specific properties:

  • ClientSideXMLClientSideXML

  • xml rootxml root

在该客户端 ADO 示例应用程序中,在客户端执行了一个简单 SQL 查询。In this client-side ADO sample application, a simple SQL query is executed on the client. 由于 ClientSideXML 属性设置为 True,因此将不带 FOR XML 子句的 SELECT 语句发送到服务器。Because the ClientSideXML property is set to True, the SELECT statement without the FOR XML clause is sent to the server. 服务器执行该查询并将一个行集返回给客户端。The server executes the query and returns a rowset to the client. 然后客户端对行集应用 FOR XML 转换,并生成 XML 文档。The client then applies the FOR XML transformation to the rowset and produces an XML document.

Xml 根属性为生成的 XML 文档提供一个顶级根元素。The xml root property provides the single top-level root element for the XML document that is generated.

备注

在代码中,必须在连接字符串中提供 Microsoft SQL ServerSQL Server 实例的名称。In the code, you must provide the name of the instance of Microsoft SQL ServerSQL Server in the connection string. 此外,本示例还指定使用 SQL ServerSQL Server Native Client (SQLNCLI11) 作为数据访问接口,该访问接口需要安装其他网络客户端软件。Also, this example specifies the use of the SQL ServerSQL Server Native Client (SQLNCLI11) for the data provider, which requires additional network client software to be installed. 有关详细信息,请参阅 SQL Server Native Client 的系统要求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 = "SELECT TOP 10 FirstName, LastName FROM Person.Contact FOR XML AUTO"  
oTestStream.Open  
oTestCommand.Properties("Output Stream").Value = oTestStream  
oTestCommand.Properties("xml root") = "root"  
oTestCommand.Execute , , adExecuteStream  
  
oTestStream.Position = 0  
oTestStream.Charset = "utf-8"  
Debug.Print oTestStream.ReadText(adReadAll)  
End Sub  
Sub Form_Load()  
 main  
End Sub