7.3 XML Access to SQL Server

  Previous section   Next section

Figure 7.1 shows a high-level architectural block diagram of SQL Server 2000's XML support. It is interesting to note that depending on the overall system or service architecture, different access components and/or protocols are preferable. If there is a limited amount of business logic, or when most of the business logic can be completely pushed either to the client (for example, via ECMAScript embedded in the HTML page) or to the database server (into the database's stored procedures), a simple HTTP or SOAP access mechanism suffices. For two-tier architectures or where the business logic needs to be performed on the Web server (the middle tier), a closer coupling of the business logic to database access is often used due to performance and programmability reasons. Thus, the XML database access story should also be accessible through the standard access components such as OLEDB, ADO, and the .NET APIs.

Figure 7.1. Architectural Overview of the XML Access to SQL Server


For these reasons, SQL Server 2000 provides all access to its XML features (except Bulkload) via the SQLOLEDB provider, ADO, and a .NET interface as well as via an IIS ISAPI extension that provides access to the functionality via HTTP and SOAP. In the following sections, we will discuss each XML feature in detail and show where it fits into the architecture and how it can be accessed both via HTTP/SOAP and ADO.NET.

7.3.1 Access via HTTP

Several ways exist to access SQL Server via HTTP. The most common one is to write an active server page that accesses the database via ADO. SQL Server 2000 introduces an HTTP access mechanism via an ISAPI extension that avoids ASP if no mid-tier business logic is needed and?with the latest Web release?can also expose templates, user-defined functions, and stored procedures as SOAP methods.

The URL formats of the new HTTP access methods start with:


The SQL Server ISAPI is registered with IIS to handle messages to a particular virtual root (vroot). The ISAPI will receive the requests for that particular vroot, and after performing authorization, will then pass the appropriate commands via the SQLOLEDB provider (in Web releases via a special-purpose SQLXML OLEDB provider) to the database. The virtual root as part of the URL provides an abstraction mechanism that encapsulates the accessed database server and database instances, the access rights, and the enabled access methods. Currently, SQL Server 2000 provides the following access methods:

  • Ad hoc URL query mechanism:


    Allows arbitrary T-SQL statements (including updates and data definition statements). Query results are returned in their native binary representation. Together with the T-SQL FOR XML extensions, it can return XML. Since this mechanism is dangerous to enable in production systems, we will not further elaborate on this mechanism.

  • Direct query access:

    http://domainname/vroot/dbobject/Table[ @column1=value] /@column2

    Provides native access to the binary representation of the data while only allowing a safer set of queries via an XPath-like simple query syntax. An example will be shortly presented where it is used.

  • Template access:


    Templates are XML documents that provide a parameterized query and update mechanism to the database. Since they hide the actual query (or update) from the user, they provide the level of decoupling that makes building loosely coupled systems possible.

    Elements in the urn:schemas-microsoft-com:xml-sql namespace are processed by the template processor and used to return database data as part of the resulting XML document. Elements in other namespaces are returned to the client unmodified. The templates support named parameters to parameterize the queries. For security purposes, only values can be parameterized and not query components.

  • XPath XML view access:


    This URL provides a way to query an XML view that is specified by the annotated schema referenced by the schema file in an ad hoc way using XPath queries. The URL can be parameterized in the same way templates are. More details on XPaths against annotated schemata are presented shortly.

  • SOAP access:


    This URL provides the WSDL (Web Services Description Language) file for the exposed Web service. Each user-configured SOAP type name provides the WSDL file and a configuration file. The services exposed via SOAP can then be accessed using any Web service access mechanism (see http://msdn.microsoft.com/sqlxml for details).

dbobject, template, and schema designate three types of virtual names for use with the ISAPI extension. They provide an abstraction for the access methods, and in the case of the last two types of virtual names, they also provide the location of the files that are associated with the access. In addition, all access mechanisms can be parameterized with predefined parameters that among others allow specifying the output encoding, the character set used in the URL, and a server-side XSLT transform.

One of the most important aspects of providing access to a database via HTTP is security. The URL access mechanism has to guarantee that only authorized people can access those parts of the database to which they are allowed access. The ISAPI extension provides three authentication modes on a per-vroot basis. This allows the database administrator to set the database internal access rights for the authenticated users inside the database since only those users who are authenti cated will be allowed access. The first mode is the standard HTTP/HTTPS-based basic authentication that prompts for a database user/password combination via HTTP (only secure with a secure HTTP connection). The second mode allows every user who connects to the server to impersonate the vroot-specific Windows or SQL Server user. The user/password combination is associated with the vroot and cannot be changed or retrieved by the user. The final authentication mode takes advantage of Windows ACL such that the authentication is done by a previous authentication event (e.g., the login to the client account), and the credentials are then passed on to the database. This allows the use of a single vroot with multiple access rights without prompting for the user/password combination.

7.3.2 Using the XML Features through SQLOLEDB, ADO, and .NET

All the XML features are also accessible through SQLOLEDB and in later Web releases via a special SQLXML OLEDB provider that provides better performance. Both of the OLEDB providers have been extended with a stream interface that is accessible via ADO's stream interface. XML and XPath were added as new dialects to SQLOLEDB. XML indicates that the input is a template file; XPath indicates that the input is an XPath query with its associated annotated schema. Using a FOR XML query as described shortly does not necessitate a new dialect, but as for any XML-specific result, the result needs to be returned via the stream interface and not the rowset interface of SQLOLEDB or ADO. Some of the predefined URL parameters such as the output encoding or the XSLT transform are also exposed as SQLOLEDB and ADO properties.

Finally the latest Web release also includes a new .NET class named Microsoft.Data.SqlXml that provides access to SQL Server and its XML features. It provides the following three primary classes:

  • SqlXmlCommand: Used to send a Transact-SQL statement to the database, execute a stored procedure, or query the database using other technologies (such as annotated schemas and XML templates) and get the results back as XML. This object supports a wide range of options, which are discussed in detail in http://msdn.microsoft.com/sqlxml.

  • SqlXmlParameter: Used to specify the value for a parameter in the command. This can be a parameter to an ad hoc query, stored procedure, XPath query, or XML template.

  • SqlXmlAdapter: Used to populate a DataSet object with an XML result set, or update the database with an XML DiffGram.

For more information about the definition and use of these APIs, the reader is referred to http://msdn.microsoft.com/sqlxml.


Part IV: Applications of XML