Executing SQL Queries (SQLXMLOLEDB Provider)

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

• ClientSideXML

• xml root

In this client-side ADO sample application, a simple SQL query is executed on the client. 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. The client then applies the FOR XML transformation to the rowset and produces an XML document.

The xml root property provides the single top-level root element for the XML document that is generated.

Note

In the code, you must provide the name of the instance of Microsoft SQL Server in the connection string. Also, this example specifies the use of the SQL Server Native Client (SQLNCLI10) for the data provider, which requires additional network client software to be installed. For more information, see System Requirements for SQL Server 2008 R2 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=SQLNCLI10;data source=SqlServerName;initial catalog=AdventureWorks2008R2;Integrated Security=SSPI ;"
oTestCommand.ActiveConnection = oTestConnection
oTestCommand.Properties("ClientSideXML") = True
oTestCommand.CommandText = "SELECT TOP 10 FirstName, LastName FROM Person.Person 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