Microsoft KB Archive/271620

= How To Retrieve XML Data by Using a SQL XML Query in a Visual Basic Client =

Article ID: 271620

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft XML Parser 2.6
 * Microsoft XML Core Services 4.0

-



This article was previously published under Q271620



SUMMARY
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.



MORE INFORMATION
 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= and 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 String Dim sQuery As String Dim outStrm sConn = &quot;Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID= ;Password= ;&quot; Dim adoConn As ADODB.Connection Set adoConn = New ADODB.Connection adoConn.ConnectionString = sConn adoConn.CursorLocation = adUseClient adoConn.Open Dim adoCmd As ADODB.Command Set adoCmd = New ADODB.Command Set adoCmd.ActiveConnection = adoConn sQuery = &quot;&quot; sQuery = sQuery & &quot;SELECT * FROM PRODUCTS FOR XML AUTO&quot;' sQuery = sQuery & &quot;&quot; Dim adoStreamQuery As ADODB.Stream Set adoStreamQuery = New ADODB.Stream '  Open the command stream so it may be written to    adoStreamQuery.Open '  Set the input command stream's text with the query string adoStreamQuery.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 = &quot;{5D531CB2-E6Ed-11D2-B252-00C04F681B71}&quot; '  Create the output stream to stream the results into. Set outStrm = CreateObject(&quot;ADODB.Stream&quot;) outStrm.Open

'  Set command's output stream to the output stream just opened. adoCmd.Properties(&quot;Output Stream&quot;) = 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, &quot;><&quot;, &quot;>&quot; & vbCrLf & &quot;<&quot;) Debug.Print str GoTo Bye RecError: Debug.Print Err.Number & &quot;: &quot; & Err.Description Bye: Set adoCmd = Nothing If adoConn.State = adStateOpen Then adoConn.Close End If   Set adoConn = Nothing End 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.</ol>

<div class="references_section">