What's New in SQLXML 4.0
Microsoft SQLXML 4.0 includes various updates and enhancements. This topic summarizes the updates and provides links to more detailed information, where available. This topic includes the following issues:
- SQLXML 4.0 Installation and Deployment
- Side-by-Side Installation Issues
- SQLXML 4.0 and MSXML
- Redistributing SQLXML 4.0
- Support for SQL Native Client
- Support for Data Types Introduced in SQL Server 2005
- XML Bulk Load Changes for SQLXML 4.0
- Registry Key Changes for SQLXML 4.0
- ADO and SQLXML 4.0 Queries
SQLXML 4.0 Installation and Deployment
SQLXML 4.0 is automatically installed when you install the Microsoft SQL Server 2005 server or tools with Notification Services.
The SQLXML 4.0 files are also installed in the following location:
%PROGRAMFILES%\SQLXML 4.0\
[!NOTA] All appropriate registry settings for SQLXML 4.0 are made as part of the installation process.
Side-by-Side Installation Issues
The installation process for SQLXML 4.0 does not remove the files that were installed by earlier versions of SQLXML. Therefore, you can have DLLs for several different version-distinctive installations of SQLXML on your computer. You can run the installations side-by-side. SQLXML 4.0 includes both version-independent and version-dependent PROGIDs. All production applications should use version-dependent PROGIDs.
SQLXML 4.0 and MSXML
SQLXML 4.0 does not install MSXML. SQLXML 4.0 uses MSXML 6.0, which is installed as part of the SQL Server 2005 installation.
Redistributing SQLXML 4.0
In general, installing SQLXML 4.0 as part of a SQL Server 2005 installation that includes Notification Services is preferred. There are also separate redistribution installation programs for SQLXML 4.0 and MSXML 6.0 (sqlxml4.msi and msxml6.msi). These can be found on the SQL Server 2005 installation CD in the following location:
%CD%\Setup\
These installation files can be used to install SQLXML 4.0 and MSXML 6.0 directly from the CD. They can also be used to freely redistribute SQLXML 4.0 and MSXML 6.0 with your own custom applications.
You will also need to redistribute SQL Native Client if you are using it as the data provider with your application. For more information, see Deploying Applications with SQL Native Client.
Support for SQL Native Client
SQLXML 4.0 supports both the SQLOLEDB and the SQL Native Client (SQLNCLI) providers, but SQLNCLI is the recommended provider because it supports features new to SQL Server 2005, such as the xml data type.
SQLNCLI is a data access technology that is new to SQL Server 2005. It combines the SQLOLEDB Provider and the SQLODBC Driver into one native dynamic link library (DLL), while also providing new functionality that is separate and distinct from the Microsoft Data Access Components (MDAC).
SQLNCLI can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features that are not supported by SQLOLEDB and SQLODBC in MDAC and Microsoft Windows. For example, SQLNCLI is required for client-side SQLXML features, such as FOR XML, to use the xml data type. For more information, see Client-side XML Formatting (SQLXML 4.0), Using ADO to Execute SQLXML 4.0 Queries, and SQL Native Client Programming.
[!NOTA] SQLXML 4.0 is not completely backward compatible with SQLXML 3.0. Because of some bug fixes and other functional changes, particularly the removal of SQLXML ISAPI support, you cannot use IIS virtual directories with SQLXML 4.0. Although most applications will run with minor modifications, you must test them before putting them into production with SQLXML 4.0.
Support for Data Types Introduced in SQL Server 2005
SQL Server 2005 introduces the xml data type, and SQLXML 4.0 introduces support for the xml data type. For more information, see xml Data Type Support in SQLXML 4.0.
For examples of how to use the xml data type in SQLXML when mapping XML views, bulk loading XML or executing XML updategrams, refer to examples provided in the following topics.
- Default Mapping of XSD Elements and Attributes to Tables and Columns
- Inserting Data by Using XML Updategrams
- Examples of Bulk Loading XML Documents
XML Bulk Load Changes for SQLXML 4.0
For SQLXML 4.0, the SchemaGen overflow field is created using the xml data type. For more information, see SQL Server XML Bulk Load Object Model.
If you have previously created Microsoft Visual Basic applications and you want to use SQLXML 4.0, you must recompile the application with reference to Xblkld4.dll.
For Visual Basic Scripting Edition applications, you must register the DLL you want to use. In the following example, if you specify version-independent PROGIDs, the application depends on the last registered DLL:
set objBulkLoad = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")[!NOTA] The version-dependent PROGID is SQLXMLBulkLoad.SQLXMLBulkLoad.4.0.
Registry Key Changes for SQLXML 4.0
In SQLXML 4.0, the registry keys have changed from the earlier releases to the following:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SQLXML4\TemplateCacheSize
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SQLXML4\SchemaCacheSize
You must change the settings if you want these keys to be in effect for SQLXML 4.0.
In addition, SQLXML 4.0 introduces the following registry keys:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SQLXML4\ReportErrorsWithSQLInfo
By default, SQLXML 4.0 returns native error information provided by OLE DB and SQL Server instead of a high-level SQLXML error (as was the case in earlier versions of SQLXML). If you do not want this behavior, the value of this registry key of type DWORD must be set to 0 (default is 1).HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SQLXML4\FORXML_GenerateGUIDBraces
By default, SQLXML return SQL Server GUID values without the enclosing braces. If you want the GUID value returned with the braces (for example, {some GUID}), value of this registry key must be set to 1 (default is 0).HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Client\SQLXML4\SQL2000CompatMode
By default, when the XML parser loads the data, white spaces are normalized according to the XML 1.0 rules. This results in loss of some of the white space characters in your data. Thus, the textual representation of your data may not be the same after parsing, although semantically the data is the same.
This key is introduced so that you can choose to keep the white space characters in the data. If you add this registry key and set its value to 0, white space characters (LF, CR, and tab) in the XML are returned encoded in case of attribute values. In case of element values, only CR is returned encoded.
For example:CREATE TABLE T( Col1 int, Col2 nvarchar(100)) GO -- Insert data with tab, line feed and carriage return). INSERT INTO T VALUES (1, 'This is a tab . This is a line feed and CR more text') GO -- Test this query (without the registry key). SELECT * FROM T FOR XML AUTO -- This is the result (no encoding of special characters). <?xml version="1.0" encoding="utf-8" ?> <r> <T Col1="1" Col2="This is a tab . This is a line feed and CR more text"/> </r> -- Now add registry key with value 0 and execute the query again. -- Note the encoding for carriage return, line-feed and tab in the attribute value. <?xml version="1.0" encoding="utf-8" ?> <r> <T Col1="1" Col2="This is a tab 	. This is a line feed and CR 
 more text"/> </r> -- Update the query and specify ELEMENTS directive SELECT * FROM T FOR XML AUTO, ELEMENTS -- Only the carriage return is returned encoded. <?xml version="1.0" encoding="utf-8" ?> <r> <T> <Col1>1</Col1> <Col2>This is a tab . This is a line feed and CR 
 more text</Col2> </T> </r>
Migration Issues for SQLXML 4.0 and SQL Server 2005
The following are issues that could impact migration of your legacy SQLXML applications to SQL Server 2005 and SQLXML 4.0.
ADO and SQLXML 4.0 Queries
In earlier versions of SQLXML, support for URL-based query execution using IIS virtual directories and the SQLXML ISAPI filter was provided. For applications that use SQLXML 4.0, this support is no longer available.
Instead, SQLXML queries, templates, and updategrams can be executed by using the SQLXML extensions to ActiveX Data Objects (ADO) first introduced in Microsoft Data Access Components (MDAC) 2.6 and later.
For more information, see Using ADO to Execute SQLXML 4.0 Queries.
Supportability for SQLXML 3.0 ISAPI and Data Types Added in SQL Server 2005
Because ISAPI support has been removed from SQLXML 4.0, if your solution requires the enhanced data typing features of SQL Server 2005 such as the xml data type or user-defined data types (UDTs) and Web-based access, you will need to use another solution such as SQLXML managed classes or another type of HTTP handler, such as Native XML Web Services for SQL Server 2005.
Alternately, if you do not require these type extensions of SQL Server 2005, you can continue to use SQLXML 3.0 to connect to SQL Server 2005 installations. The SQLXML 3.0 ISAPI support will work against SQL Server 2005 but does not support or recognize the xml data type or UDT type support introduced in SQL Server 2005.
XML Bulk Load Security Changes for Temporary Files
For SQLXML 4.0 and SQL Server 2005, XML Bulk Load file permissions are granted to the user executing the bulk load operation. Read and Write permissions are inherited from the file system. In previous releases of SQLXML and SQL Server, XML Bulk Load under SQLXML would create temporary files that were not secured and could be readable by anyone.
Migration Issues for Client-Side FOR XML
Due to changes in the execution engine, SQL Server 2005 may return different values in the metadata for a base table than would be returned if the FOR XML query was executed under SQL Server 2000. In cases where this occurs, client-side formatting of the FOR XML query results will have differing output depending on whether the same query is run against SQL Server 2000 or SQL Server 2005.
If a FOR XML query is executed client-side using SQLXML 3.0 over an xml data type column, the data in the results will come back as a fully entitized string. In SQLXML 4.0, if the SQL Native Client (SQLNCLI) is specified as the provider, the data will be returned as XML.