Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

↑ Back to the top


More information

  1. Create a new Visual Basic Standard EXE. Form1 is created by default.
  2. On the Project menu, select References, and then set a reference to Microsoft ActiveX Data Objects 2.6.
  3. 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 String
        Dim sQuery As String
        Dim outStrm
            
        sConn = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=Northwind;User ID=<username>;Password=<strong password>;"
        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 = "<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.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 = "{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 str
        GoTo Bye
        
    RecError:
        Debug.Print Err.Number & ": " & Err.Description
        
    Bye:
        Set adoCmd = Nothing
        If adoConn.State = adStateOpen Then
            adoConn.Close
        End If
        Set adoConn = Nothing
    End Sub 
    					
  4. 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.

↑ Back to the top


References

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
316364 How To Quote a Member of the Connection String in ADO.NET By Using Visual Basic .NET
307224 How To Use XML in Connected and Disconnected ADO.NET Applications
For information about SQL 2000 and mapping schema, see SQL Server Books Online.

For XML, please see the XML information on MSDN on the following Microsoft Web site:

↑ Back to the top


Keywords: KB271620, kbhowto

↑ Back to the top

Article Info
Article ID : 271620
Revision : 10
Created on : 7/13/2004
Published on : 7/13/2004
Exists online : False
Views : 553