XML-Related SQLOLEDB Provider Properties

The SQLOLEDB provider makes many properties available through the ADO Command object to make working with XML easier. These properties are discussed in the following sections.

Applying a Style Sheet with the XSL Property

You could encounter many situations in which you'll want to apply an XSL style sheet to the XML data as you retrieve it. For example, the Northwind Traders invoice might need to be rendered as HTML or transformed into a different XML grammar before being sent to the customer.

To apply a style sheet to XML data retrieved over ADO, the XSL property of the Command object can be set to the file path of the XSL file that you need to apply. For example, the following Microsoft Visual Basic code applies a style sheet named invoice.xsl, which is stored in the directory where the application is located.

 cmdXML.Properties("xsl") = App.Path & "\invoice.xsl" 

The results returned through the output stream will now reflect the XML data after it has been transformed by the style sheet. The specified path can also be a URL, allowing you to store your style sheets on a remote Web server, as shown in the following sample code:

 cmdXML.Properties("xsl") = "http://myWebserver/invoice.xsl" 

To perform the same task in a Microsoft Active Server Page (ASP), you can use the Server.MapPath method to access a style sheet, which you store in the same virtual directory as the ASP.

Using Relative Paths and the Base Path Property

Instead of using absolute paths to reference files, you can set the Base Path property of the Command object. Once this property has been set, all other files, such as style sheets, can be referenced using a relative path.

In the following example, the application directory is specified as the Base Path; all subsequent file references can then be relative:

 cmdXML.Properties("Base Path") = App.Path cmdXML.Properties("xsl") = "invoice.xsl" 

As with the XSL property, the Base Path property can be used to reference a virtual root or a Web folder using a URL:

 cmdXML.Properties("Base Path") = "http://myWebserver" cmdXML.Properties("xsl") = "invoice.xsl" 

Controlling File References with the SS Stream Flags Property

The SS Stream Flags property is used to configure the way ADO accesses files such as style sheets. This property can be used as a security measure to allow files such as style sheets to be selected dynamically at run time while restricting the locations in which those files can be accessed.

A number of constant values can be assigned to the SS Stream Flags property. For example, the STREAM_FLAGS_DISALLOW_URL constant, which has a value of 1, can be used to prevent a URL from being used for the XSL or Base Path property, as shown here:

 cmdXML.Properties("SS STREAM FLAGS")= STREAM_FLAGS_DISALLOW_URL 

You can specify multiple constants by using the OR operator. For example, the following code disallows URLs and absolute paths, ensuring that all file references are relative to the directory where the application is located.

 cmdXML.Properties("Base Path") = App.Path cmdXML.Properties("SS STREAM FLAGS")= _     STREAM_FLAGS_DISALLOW_URL Or STREAM_FLAGS_DISALLOW_ABSOLUTE_PATH 

The full list of constants and their uses appears here:

• STREAM_FLAGS_DISALLOW_URL Prevents the use of a URL for the Base Path, XSL, or Mapping Schema property. The value for this constant is 1.
• STREAM_FLAGS_DISALLOW_ABSOLUTE_PATH Prevents the use of an absolute path reference. All files must be relative to the Base Path property. The value for this constant is 2.
• STREAM_FLAGS_DISALLOW_QUERY Prevents a query from being passed to SQL Server. (Use this constant to restrict access so that only mapping schemas can be used to access data.) The value for this constant is 4.
• STREAM_FLAGS_DONTCACHEMAPPINGSCHEMA Prevents a mapping schema from being cached. The value for this constant is 8.
• STREAM_FLAGS_DONTCACHETEMPLATE Prevents a query template from being cached. The value for this constant is 16.
• STREAM_FLAGS_DONTCACHEXSL Prevents a style sheet from being cached. The value for this constant is 32.

Many of the constants relate to the use of the Mapping Schema property. I'll talk about this property in the "Mapping Schemas" section of Chapter 6.

Managing the Output Format with the Output Encoding Property

Until now, we've processed the retrieved XML data by reading the text from the output stream. You can also send the stream to a browser through the Response object in an ASP script. In fact, if you're retrieving XML data using ADO from an ASP, you can simply specify the Response object as the output stream, as shown here:

 <% cmdXML.Properties("Output Stream") = Response %> 

This code causes the results of the query to be sent straight to the browser when the query is executed.

The way the data is displayed in the browser depends on the encoding used in the stream. You can control this encoding by setting the Output Encoding property to a valid encoding string, such as UTF-8 or Unicode. For example, the following code explicitly sets the encoding to Unicode:

 cmdXML.Properties("Output Encoding") = "Unicode"