OLE DB Provider for SQL Server Extensions for XML

The Microsoft® OLE DB Provider for SQL Server (SQLOLEDB) supports a new dialect called DBGUID_MSSQLXML to execute XML templates with embedded queries (such as SQL FOR XML and XPath queries). Templates are valid XML documents containing one or more queries. The FOR XML and XPath queries return a document fragment. The templates act as a container for the resulting document fragment.

Setting an XML Command Using ICommandText

The ICommandText::SetCommandText and ICommand::Execute methods have been enhanced to allow XML documents to be set as command text, to execute the command and retrieve the results as a stream that can then be used in further processing, such as passing the XML document to DOM (Document Object Model).

The XML templates can be passed to the ICommandText::SetCommandText method. When XML templates are set as command text using ICommandText::SetCommandText, the consumer must pass DBGUID_MSSQLXML as the GUID of the command syntax. This new GUID indicates that the command text is an XML template.

The consumer must call ICommand::Execute to execute XML templates. To obtain an XML document as a result set, the riid is set to IStream, in which case the provider returns the result set as a stream.

Limitations of ICommandText

The template being passed to ICommandText::SetCommandText can be large. And if the template being executed is stored in a file, overhead is required to read the file, buffer its contents, and then set command text using ICommandText::SetCommandText.

In addition, the ICommandText::SetCommandText expects the command string to be a Unicode string. If the actual XML file is in some encoding, additional overhead is required to convert the file to Unicode before passing it to IcommandText::SetCommandText as a command.

Setting an XML Command Using ICommandStream

The OLE DB (version 2.6) interface ICommandStream, although similar to ICommandText, passes a command as a stream object rather than as a string.

SQLOLEDB has implemented the optional ICommandStream interface on the command object. The ICommandStream interface allows you to pass a stream to the command object.

The ICommandStream interface allows a command to be in any encoding that the XML parser understands. Thus, when ICommand::Execute is called, the command text is read out of the stream directly and no conversion is required. Executing XML commands using ICommandStream interface is more efficient.

Both the ICommandStream::GetCommandStream and ICommandStream::SetCommandStream interfaces are implemented in SQLOLEDB.

For ICommandStream, the default dialect (DBGUID_DEFAULT) is DBGUID_MSSQLXML. The dialects supported by ICommandStream::SetCommandStream are provider-specific. SQLOLEDB supports DBGUID_MSSSQLXML only (DBGUID_SQL and DBGUID_XPATH are not supported.)

If you read from the stream returned by GetCommandStream before EXECUTE is called, EXECUTE may fail unless EXECUTE can read from the proper position in the stream.

Support for the OLE DB (Version 2.6) DBPROPSET_STREAM Property Set

SQLOLEDB has implemented DBPROPSET_STREAM property set (in the Stream property group), which includes these properties:

DBPROP_OUTPUTSTREAM

The value passed in this property is a Variant containing a pointer to either IStream or ISequentialStream. When this property is set, ICommand::Execute returns results in the stream specified by this property. This avoids extra copies of the data because you can pass the stream to other users, such as the XML parser.

DBPROP_OUTPUTENCODING

This property specifies the requested encoding for the stream returned by the Execute method. Some of the commonly used encodings are UTF-8, ANSI, and Unicode. The UTF-8 is the default encoding if the value of this property is NULL.

Requesting ISequentialStream on ICommand::Execute

You can request ISequentialStream on ICommand::Execute.

While reading from a stream as long as there is data to read, ISequentialStream::Read will return S_OK. After the end of the stream is reached, a subsequent read will return S_FALSE, unless there were errors during the execution of the command. If there were any errors, DB_S_ERRORSOCCURED is returned only on the first read after the end of the stream was reached. All the subsequent reads will return S_FALSE.

In executing the command, if there are any errors, the errors are returned as processing instructions (PIs) in the stream. All the errors are returned after the last read. Applications that do not have access to error objects can examine the stream contents for the PI containing the errors.

ISequentialStream is supported only when the selected result is a single-column rowset.

SQLOLEDB Provider-Specific Properties

To support XML-specific behavior, SQLOLEDB has implemented the following provider-specific properties in the DBPROPSET_SQLSERVERSTREAM property set (Stream property group). These properties allow you to specify the mapping schema against which an XPath query can be specified as a command, or to specify an XSL file to process the result. Some of these properties are useful for enhancing security and performance.

SSPROP_STREAM_BASEPATH

This property is used to specify the base path. This base path is used in resolving relative paths specified for the XSL file, mapping schema, or external schema references in a template.

SSPROP_STREAM_MAPPINGSCHEMA

This property is used for specifying a schema for the XPath queries. The path specified can be relative or absolute. If the path specified is relative, the base path specified in SSPROP_STREAM_BASEPATH is used to resolve the relative path. If the base path is not specified, the relative path is relative to the current directory

SSPROP_STREAM_XSL

This property is used for specifying an XSL file. The path specified can be relative or absolute. If the path specified is relative, the base path specified in SSPROP_STREAM_BASEPATH is used to resolve the relative path. If the base path is not specified, the relative path is relative to the current directory.

SSPROP_STREAM_CONTENTTYPE

If an XSL style sheet is applied to the result, the media-type property on <xsl:output> in the XSL file is returned as the value of this property.

SSPROP_STREAM_FLAGS

This property is used to specify certain security restrictions. For example, you may not want to allow URL references to files or absolute paths to files (such as external sites). You may not want to allow queries in the templates. The property can be assigned values STREAM_FLAGS_DISALLOW_URL, STREAM_FLAGS_DISALLOW_ABSOLUTE_PATH, or STREAM_FLAGS_DISALLOW_QUERY.

For more information about these properties, see Initialization and Authorization Properties.