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 Data Using a Template File in ATL OLE DB

View products that this article applies to.


OLEDBTemplate.exe demonstrates how to extract an XML stream from a SQL Server 2000 database using an XML template file.

↑ Back to the top

More information

The following file is available for download from the Microsoft Download Center:
Release Date: Oct. 12, 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 OLEDBTemplate.exe file contains the following files:

File nameSize
EULA.txt2 KB
Products.xml1 KB
Products.xsl3 KB
SeqStream.h4 KB
OLEDBTemplate.cpp5 KB

This sample shows how an Active Template Library (ATL) OLE DB client can execute a SQL XML query stored in a template file and retrieve the XML data from a SQL Server 2000 database using the Microsoft SQL Server OLE DB Provider (SQLOLEDB) that ships with Microsoft Data Access Components (MDAC) version 2.6. The Products.xml template file contains the following parameterized SQL XML query:
<?xml version="1.0" ?> 
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
      <sql:param name="ProdName">%</sql:param> 
   <sql:query>SELECT * FROM Products WHERE ProductName like '%' + @ProdName + '%' ORDER BY ProductName FOR XML AUTO</sql:query> 
This query uses the wildcard % to ensure that if no parameter is passed to it, the query is effectively reduced to a non-parameterized query.

In addition, this sample provides an ISequentialStream derived class, CSequentialStream, that provides the capability to read from a file. This provides the input stream object for the ICommandStream interface. The CSequentialStream class can be found in SQL Server 2000 Books Online as well.

The sample configures an input XML stream by means of a CSequentialStream object, executes a SQL XML query stored within a template file, and retrieves the XML data in an ISequentialStream stream. Depending on whether the Products.xsl stylesheet is used for processing the output XML stream, the following output files are generated:
  • Queryout.htm, when XSL is used.
  • Queryout.xml, when XSL is not used.
The sample does the following to set up an ATL OLE DB client to retrieve XML data as a result of executing a SQL XML query stored in a template file:
  1. It configures an input stream to load the Products.xml template file:
    CSequentialStream *pXMLInput;				
    pXMLInput = new CSequentialStream(L"Products.xml");	
  2. It sets up the SQLOLEDB property-set DBPROPSET_SQLSERVERSTREAM for configuring the input stream:
  3. It sets the Base Path property to the folder that contains the template file and the optional .xsl file:
    propset.AddProperty(SSPROP_STREAM_BASEPATH, (LPCWSTR)m_path);
  4. (Optional) It specifies the Products.xsl XSL file to process the XML data that is retrieved and output it in HTML format:
    propset.AddProperty(SSPROP_STREAM_XSL, OLESTR("Products.xsl"));
  5. It sets the ICommandStream interface to contain the input stream:
    if(FAILED(hr = m_spCommand->QueryInterface(&pCommandStream)))
      printf("Failed to get an ICommandStream interface...\n");
      return hr;
    if(FAILED(hr = pCommandStream->SetCommandStream(IID_ISequentialStream, DBGUID_DEFAULT, (ISequentialStream*) pXMLInput )))
      printf("Failed to set command stream.\n");
      return hr;
  6. It sets up an output stream to retrieve the XML data and execute:
    m_spCommand->Execute(NULL, IID_ISequentialStream, NULL, NULL, (IUnknown **) &pIXMLOutput );

Steps to Run the Sample

  1. Create an empty Win32 console application.
  2. Insert the OLEDBTemplate.cpp file into the project.
  3. Insert the SeqStream.h file into the project.
  4. Copy Products.xml into the project folder.
  5. Copy Products.xsl into the project folder.
  6. Modify the connection string to refer to a valid SQL Server 2000 database.
  7. Compile and then run the application.

↑ Back to the top


SQL Server 2000 Books OnlineOLE DB 2.6 Documentation

↑ Back to the top

Keywords: KB272181, kbsample, kbhowto, kbfile, kbdtl, kbdownload

↑ Back to the top

Article Info
Article ID : 272181
Revision : 8
Created on : 8/5/2004
Published on : 8/5/2004
Exists online : False
Views : 444