Symptoms
If you run an ADODB command stream and specify SQL SELECT with the FOR XML AUTO clause, an XML document stream is fetched from SQL Server and displayed in the Visual Basic Intermediate window.
You must set the following properties for the Properties collection of the ADODB.Command object: Output Stream. This property designates where the resulting XML data stream will be piped.Dialect. The dialect defines the syntax and the general rules that the provider uses to parse the string or the stream. By setting the command language dialect, you specify how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ActiveX Data Objects (ADO). The dialect is specified by a globally unique identifier (GUID) and is set by using the Dialect property of the Command object.
Resolution
Create a new Visual Basic Standard EXE. Form1 is created by default. On the Project menu, select References, and then set a reference to Microsoft ActiveX Data Objects 2.6.Place a CommandButton on Form1, and then place the following code in its click event:
Note You must change User ID=<username> and Password=<strong password> to the correct values before you run this code. Make sure that the User ID has the appropriate permissions to perform this operation on the database.

Private Sub Command1_Click()Dim sConn As StringDim sQuery As StringDim outStrmsConn = “Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID=<username>;Password=<strong password>;”Dim adoConn As ADODB.ConnectionSet adoConn = New ADODB.ConnectionadoConn.ConnectionString = sConnadoConn.CursorLocation = adUseClientadoConn.OpenDim adoCmd As ADODB.CommandSet adoCmd = New ADODB.CommandSet adoCmd.ActiveConnection = adoConnsQuery = “<ROOT xmlns:sql=’urn:schemas-microsoft-com:xml-sql’>”sQuery = sQuery & “<sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>”’sQuery = sQuery & “</ROOT>”Dim adoStreamQuery As ADODB.StreamSet adoStreamQuery = New ADODB.Stream’Open the command stream so it may be written toadoStreamQuery.Open’Set the input command stream’s text with the query stringadoStreamQuery.WriteText sQuery, adWriteChar’Reset the position in the stream, otherwise it will be at EOS.adoStreamQuery.Position = 0′Set the command object’s command to the input stream set above.Set adoCmd.CommandStream = adoStreamQuery’Set the dialect for the command stream to be a SQL query.adoCmd.Dialect = “{5D531CB2-E6Ed-11D2-B252-00C04F681B71}”‘Create the output stream to stream the results into.Set outStrm = CreateObject(“ADODB.Stream”)outStrm.Open’Set command’s output stream to the output stream just opened.adoCmd.Properties(“Output Stream”) = outStrm’Execute the command, thus filling the output stream.adoCmd.Execute , , adExecuteStream’Position the output stream back to the beginning of the stream.outStrm.Position = 0′Create temporary string.Dim str As String’Assign the stream’s output to the temp string to format.str = outStrm.ReadText(-1)’Add a cr/lf pair for each row in the result stream.str = Replace(str, “><”, “>” & vbCrLf & “<”)Debug.Print strGoTo ByeRecError:Debug.Print Err.Number & “: ” & Err.DescriptionBye:Set adoCmd = NothingIf adoConn.State = adStateOpen ThenadoConn.CloseEnd IfSet adoConn = NothingEnd Sub Specify either the SQL 2000 Server or, if the server is on your local machine, use the period symbol (.) or (local). Note that the Immediate window of Visual Basic displays the results.