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.

SAMPLE: How To Retrieve XML Data Using an SQL XML Query with ADO in Visual C++


View products that this article applies to.

This article was previously published under Q271780

↑ Back to the top


Summary

XMLStream.exe demonstrates how to create an ActiveX Data Objects (ADO) client that uses an SQL XML query to extract an XML stream from a SQL Server 2000 server.

↑ Back to the top


More information

This sample requires the client to connect to a SQL Server 2000 server by using the Microsoft OLE DB Provider for SQL Server that ships with Microsoft Data Access Components (MDAC) version 2.6 or later only. Prior versions of SQL Server always returned data in some form of a recordset as a result of executing a 'SELECT..' SQL statement. In SQL Server 2000, the 'SELECT ...' SQL Statement has been enhanced to include a new FOR XML clause, frequently called an SQL XML query. This clause allows SQL Server to return data in the form of an XML document.

In addition, the ADO Command object has been enhanced to configure and retrieve XML data as a result of executing "SELECT ... FOR XML" SQL queries.

XMLStream.exe provides an example that does the following:
  1. Sets up the connection to a SQL Server 2000 database:
    _bstr_t m_Conn("PROVIDER=SQLOLEDB.1;Server=SQL2000Srv;Database=Northwind;uid=sa;pwd=;");
    					
  2. Sets up the SQL XML query:
    _bstr_t m_Qry("<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
                  <sql:query>SELECT * FROM PRODUCTS FOR XML AUTO</sql:query>
                  </ROOT>");
    					
  3. Configures the input Stream object to contain the SQL XML query:
    hr = strm->Open(vtEmpty,adModeUnknown,adOpenStreamUnspecified,L"",L"");
    hr = strm->WriteText(m_Qry,adWriteChar);
    					
  4. Sets the CommandStream property of the ADO Command object to the input Stream:
    hr = cmd->putref_CommandStream(strm);
    					
  5. Assigns the SQL XML globally unique identifier (GUID) to the Dialect property of the Command object:

    NOTE: Dialect defines the syntax and general rules that the provider uses to parse the string or stream. Setting the command language dialect specifies how the Microsoft OLE DB Provider for SQL Server interprets the command text that is received from ADO.
    hr = cmd->put_Dialect(_bstr_t("{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"));
    					
  6. Sets up an output Stream to retrieve the XML data:
    hr = cmd->Properties->Item[L"Output Stream"]->put_Value(_variant_t((IDispatch*) outStrm));
    					
  7. Executes the Command by using the adExecuteStream enumeration:
    hr = cmd->Execute(&vra,&vtEmpty,adExecuteStream);
    					

Steps to Run the Sample

  1. Unzip XMLStream.exe.
  2. Create an empty Win32 console application.
  3. Insert the XMLStream.cpp file into the project.
  4. Modify the connection string to refer to a valid SQL Server 2000 database.
  5. From the Project menu, click Settings, and then click the C/C++ tab. In the Project Options box, change /MLd to /MTd.
  6. Compile and then run the application.
The following file is available for download from the Microsoft Download Center:
Release Date: September 27, 2000

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591� How to Obtain Microsoft Support Files from Online Services
Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file. The XMLStream.exe file contains the following files:

Collapse this tableExpand this table
File nameSize
EULA.txt2 KB
XMLStream.cpp6 KB

↑ Back to the top


References

For additional information on XPATH query and XML , click the article numbers below to view the articles in the Microsoft Knowledge Base:
272177� SAMPLE: How to Retrieve XML Data in VC++ Using an XPATH Query
272185� SAMPLE: How to Retrieve Data Using a SQL XML Query in ATL OLE DB
271782� SAMPLE: How To Retrieve XML Data Using an XPATH query with ADO in Visual C++
271775� SAMPLE: HowTo Retrieve Relational Data Using OpenXML with ADO in Visual C++
272180� SAMPLE: How to Retrieve XML Data Using a Template File with ADO in Visual C++
Microsoft SQL Server 2000 Books Online

ADO 2.6 Documentation

↑ Back to the top


Keywords: kbdownload, kbfile, kbhowto, kbmsxmlnosweep, KB271780

↑ Back to the top

Article Info
Article ID : 271780
Revision : 8
Created on : 8/9/2004
Published on : 8/9/2004
Exists online : False
Views : 208