Knowledge Base

SAMPLE: How to Retrieve XML Data Using a Template File with ADO in Visual C++

Article ID: 272180

Article Last Modified on 8/5/2004

APPLIES TO

Microsoft Data Access Components 2.6
Microsoft Data Access Components 2.7
Microsoft SQL Server 2000 Standard Edition
Microsoft Visual C++ 6.0 Enterprise Edition
Microsoft Visual C++ 6.0 Professional Edition
Microsoft Visual C++ 6.0 Standard Edition
Microsoft ActiveX Data Objects 2.6
Microsoft ActiveX Data Objects 2.7

This article was previously published under Q272180

SUMMARY

XMLTemplate.exe demonstrates how to extract an XML stream from a SQL Server 2000 database using an XML Template file and the ADO Command object.

MORE INFORMATION

The following file is available for download from the Microsoft Download Center:

XMLTemplate.exe

Release Date: Jan. 6, 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 XMLTemplate.exe file contains the following files:

File name	Size
EULA.txt	2 KB
Readme.txt	4 KB
ADOHeader.h	1 KB
Resource.h	1 KB
Products.xml	1 KB
Products.xsl	3 KB
StdAfx.cpp	1 KB
StdAfx.h	2 KB
WebBrowser.cpp	11 KB
WebBrowser.h	4 KB
XMLTemplate.cpp	3 KB
XMLTemplate.dsp	5 KB
XMLTemplate.dsw	1 KB
XMLTemplate.h	2 KB
XMLTemplate.rc	6 KB
XMLTemplateDlg.cpp	8 KB
XMLTemplateDlg.h	2 KB
\Res\XMLTemplate.ico	2 KB
\Res\XMLTemplate.rc2	2 KB

This sample shows how an ADO client can run a SQL Server XML query stored in a template file and retrieve the XML data from a SQL Server 2000 database by using the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) that ships with MDAC 2.6 or later.

The template file, Products.xml, contains the following parameterized SQL XML query:

<?xml version="1.0" ?> 
<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
   <sql:header>
      <sql:param name="ProdName">%</sql:param> 
   </sql:header>
   <sql:query>SELECT * FROM Products WHERE ProductName like '%' + @ProdName + '%' ORDER BY ProductName FOR XML AUTO</sql:query> 
</root>

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.

The sample configures an input XML stream through an ADO Stream object, runs the SQL XML query through an ADO Command object, and then retrieves the output XML stream in an ADO Stream object. Depending on whether or not the XSL stylesheet Products.xsl is used for processing, the output XML stream the output files generated are:

Queryout.htm when you use XSL.

-and-

Queryout.xml when you do not use XSL.

The sample does the following:

Sets up a connection to a SQL Server 2000 database:

Note The uid <user name> value must have the appropriate permissions to perform these operations on the database.
```
_bstr_t m_Conn("PROVIDER=SQLOLEDB.1;Data Source=SQL2000Srv;Database=Northwind;uid=<user name>;pwd=<strong password>;");
```

Configures the input Stream to load the template file Products.xml:

hr = strmCmd->Open(vtEmpty,ado26::adModeUnknown,ado26::adOpenStreamUnspecified,L"",L"");
strmCmd->PutCharset(L"ascii");
strmCmd->PutType(ado26::adTypeText);
hr = strmCmd->LoadFromFile(L"C:\\MyTemplateFilePath\\products.xml");

Sets the ADO Command object's CommandStream property to the input Stream:
```
hr = cmd->putref_CommandStream(strmCmd);
```

Sets up an output Stream to retrieve the XML data:

hr = cmd->Properties->Item[L"Output Stream"]->put_Value(_variant_t((IDispatch*) strmOutput));

(Optional) Specifies the XSL file, Products.xsl, to process the XML data retrieved and output it in an HTML format:
```
hr = cmd->Properties->Item[L"XSL"]->put_Value(_variant_t(L"products.xsl"));
```
Runs the Command by using the adExecuteStream enumeration:
```
hr = cmd->Execute(&vra,&vtEmpty,adExecuteStream);
```

Steps to Run the Sample

Unzip XMLTemplate.exe.
Modify the connection string in XMLTemplateDlg.cpp to refer to a valid SQL Server 2000 database.
Compile and then run the application.

REFERENCES

For additional information272185, click the article numberSAMPLE: How to Retrieve Data Using a SQL XML Query in ATL OLE DB below to view the articleSAMPLE: How to Retrieve Data Using a SQL XML Query in ATL OLE DB in the Microsoft Knowledge Base:

%3 %4

271780 SAMPLE: How To Retrieve XML Data using a SQL XML Query with ADO in Visual C++

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

%1 %2

Microsoft SQL Server 2000 Books OnLine

ADO 2.6 Documentation

Additional query words: XMLTemplate exe

Keywords: kbinfo kbdownload kbfile kbmsxmlnosweep KB272180

Microsoft KB Archive/272180

Contents