Article ID: 271620 - Last Review: July 13, 2004 - Revision: 5.5

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

View products that this article applies to.

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.

Back to the top

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=<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

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 (http://support.microsoft.com/kb/316364/ ) How To Quote a Member of the Connection String in ADO.NET By Using Visual Basic .NET

307224 (http://support.microsoft.com/kb/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:

http://msdn.microsoft.com/xml (http://msdn.microsoft.com/xml)

Back to the top