12. SQL Server As a Platform for Web Services
WEB SERVICES ARE directly integrated into SQL Server 2005. Web Services do not require a library to be distributed to clients and are widely supported across all languages and platforms commonly in use today. Prior to SQL Server 2005, the only way to communicate with SQL Server was with Microsoft-supplied libraries that had to be distributed to clients.
Mixing Databases and Web Services
SQL Server 2005 adds another protocol that clients can use to communicate directly with SQL Server: SOAP over HTTP, which is Web Services.
Prior to SQL Server 2005, a proprietary protocol, TDS (Tabular Data Stream), was used by clients to communicate directly with SQL Server. TDS is a proprietary and undocumented protocol, and requires a set of client network libraries. These libraries are the SQL Server network libraries, and Microsoft distributes them only on Windows operating systems.
SQL Server supports using old versions of the TDS protocol for compatibility purposes, but only with limited functionality. Even the libraries distributed by Sybase for its database system will work with SQL Server because Sybase also uses TDS; SQL Server and Sybase at one time used a common codebase. Access to the full functionality of SQL Server 2005 via TDS requires the latest network libraries that are distributed by Microsoft.
Web Services expose a standard mechanism for communication that uses standard protocols and message formats. The network protocol most often used is HTTP, and the message format is known as SOAP. SOAP is described in a set of recommendations from the World Wide Web Consortium. Aprimer is available at http://www.w3.org/TR/2003/REC-soap12-part0-20030624/.
SOAP over HTTP has become a popular means of communication because it does not depend on particular libraries being distributed to clients and can even be used between applications running on different operating systems. The systems involved need only to support HTTP and XML, which is the case for virtually all languages and operating systems in common use today.
In practice, it is possible to connect to SQL Server using TDS through a firewall, and it is not all that uncommon. But the techniques for this are not as familiar as the ones for letting HTTP connections go through a firewall. SQL Server 2005 includes many new features that extend its utility far beyond being a repository for corporate data. These new features, along with the fact that Web Services are integrated directly into SQL Server 2005, will lead to use cases for SQL Server we would never have thought of before.
Web Services integrated directly into SQL Server 2005 are very useful, even if we ignore the capability to punch through a firewall. They offer connectivity to SQL Server without the requirement to deploy a client library and keep it up to date, even when those “clients” are middle-tier servers. Web Services themselves are being widely deployed. With SQL Server 2005, middle-tier servers can relay on a Web Service message directly to SQL Server without having to process it. And, of course, those middle-tier servers might not even be running Windows as an operating system.
It’s a bit of a red herring to think of Web Services integrated directly into SQL Server 2005 as just providing a standard of punching through a firewall, because it adds much more than that. In the past, SQL Server has supported connectivity though Banyan Vines and Novell Netware because at the time, they were in wide use. Think of Web Services integrated directly into SQL Server 2005 as just another networking technology that is supported by SQL Server.
SQL Server 2000 also allowed communication via HTTP and Web Services, but it depended on Internet Information Server (IIS) for HTTP and SOAP support. It did this by adding a custom ISAPI (Internet Server Application Program Interface) filter and extensions to IIS; in other words, it created an ISAPI application, which is the standard way to extend IIS.
This ISAPI application could be configured by an MMC (Microsoft Management Console) snap-in to add virtual directories to IIS and map those virtual directories to functionality in SQL Server. The ISAPI application would listen for appropriately formatted HTTP requests, convert them to TDS commands, send the commands to SQL Server, and return the results in the form of XML as the HTTP response. It provided access to SQL Server through user-configured batches, called templates, and arbitrary SQL expressions—all with appropriate levels of security, of course.
Over time, this functionality was improved and distributed though Web releases and called SQLXML. These releases added features that allowed XML to be used to update SQL Server and to map stored procedures and function to Web Services. In effect, this turned SQL Server into a Web Service.
SQLXML and the ISAPI filter and extensions needed to make it work are not distributed with SQL Server 2005, but all the capabilities of SQLXML 3.0 are available in SQL 2005. This means that applications that currently use SQLXML will not have to be upgraded to use SQL Server 2005. They will operate as they always did, although through IIS.
SQL Server 2000 depended on IIS to implement the HTTP protocol for it. Windows Server 2003 and Windows XP with Service Pack 2 (SP2) include a driver, http.sys, that fully implements the HTTP protocol. SQL Server 2005 uses http.sys to implement the HTTP protocol, which means that it has no dependency on IIS but does require Windows Server 2003 or Windows XP with SP2 as a host to support Web Services.
For a number of reasons, http.sys is a more efficient implementation of HTTP than the previous implementation used in Windows—for example, the implementations in IIS prior to IIS6. One of the key reasons is that http.sys fully implements HTTP in a kernel mode driver. Typically, applications will make fewer round trips between user and kernel mode with http.sys. Also, because http.sys is a driver, a single implementation can be used by multiple applications at the same time. This means that if appropriately configured, two applications can listen on port 80 at the same time. In general, http.sys is a better implementation of HTTP overall than previous versions.
HTTP Endpoint Declaration
In SQLXML 3.0, we could expose any stored procedure or function or allow a Transact-SQL (T-SQL) script to be executed over HTTP. We have the same capabilities in SQL Server 2005 by using an HTTP endpoint to implement a Web Service. In SQLXML 3.0, the procedures and functions were exposed as Web Services, and the query string of an HTTP GET command could be passed to SQL Server as a T-SQL script. In SQL Server 2005, all these are exposed as Web Services.
The way that we defined an HTTP endpoint with SQLXML 3.0 was to use either a COM object model that wrote to the IIS metabase and the Windows Registry or to use an MMC snap-in that provided a graphic interface to configure it. This functionality is now built directly into SQL Server 2005. The information itself is stored in SQL Server, not in an external file or the Windows Registry. T-SQL expressions are used to define, drop, and alter it. The relevant DDL statements are CREATE ENDPOINT, ALTER ENDPOINT, and DROP ENDPOINT. You can use these DDL statements to define endpoints for protocols other than HTTP (for example, SQL Server Service Broker endpoints) and for features other than Web Services, but in this chapter, we’ll cover only using them to define HTTP endpoints for Web Services. We’ll discuss them here and at the same time correlate these DDL statements with the COM object model that you use in SQLXML 3.0. Listing 12-1 shows the complete syntax for defining an HTTP endpoint in T-SQL.
Listing 12-1. Syntax for Web Service
CREATE ENDPOINT endPointName [AUTHORIZATION <login>]
[ STATE = { STARTED | STOPPED | DISABLED } ]
AS HTTP
( [ SITE = {'*' | '+' | 'webSite' } ,]
PATH = ' url'
, PORTS = ({CLEAR | SSL} [,... n])
[, CLEAR_PORT = clearPort ]
[, SSL_PORT = SSLPort ]
, AUTHENTICATION =({BASIC | DIGEST |
NTLM | KERBEROS | INTEGRATED} [,...n])
[, AUTH_REALM = { 'realm' | NONE } ]
[, DEFAULT_LOGON_DOMAIN = {'domain' | NONE } ]
[, COMPRESSION = { ENABLED | DISABLED } ]
)
[ FOR SOAP (
[ { WEBMETHOD ['namespace' .] 'methodalias' (
NAME = three.part.name
[, SCHEMA = { NONE | STANDARD | DEFAULT }]
[, FORMAT = { NONE | ALL_RESULTS | ROWSETS_ONLY }])
} [,... n] ]
[ BATCHES = { ENABLED | DISABLED } ]
[ , WSDL = { NONE | DEFAULT | 'sp_name' } ]
[ , SESSIONS = { ENABLED | DISABLED } ]
[ , SESSION_TIMEOUT = {int | NEVER}]
[ , DATABASE = { 'database_name' | DEFAULT } ]
[ , NAMESPACE = { 'namespace' | DEFAULT } ]
[ , SCHEMA = { NONE | STANDARD } ]
[, CHARACTER_SET = { SQL | XML } ]
[, HEADER_LIMIT = int ]
)
This syntax, seen in its entirety, may seem imposing at first, but it is broken into two major sections. The AS HTTP section specifies the characteristics of the endpoint itself. The FOR SOAP section specifies the characteristics of the Web Service hosted at that endpoint.
We will start by looking a simple example of a Web Service so we can see how the major parts work. This example will expose a stored procedure, RepairEstimate, as a Web Service. Given a car model as input, RepairEstimate will return an estimate of the cost to repair it. There are several things besides creating an HTTP endpoint that we will need to do to make a working Web Service. Listing 12-2 shows a batch that creates this Web Service.
Listing 12-2. RepairPoint Web Service
CREATE DATABASE Repairs
use Repairs
CREATE LOGIN [Canopus5MiniDan] FROM WINDOWS
CREATE Function RepairEstimate(@model NVARCHAR(10))
RETURNS INT
AS
BEGIN
RETURN 12
END
CREATE USER [Canopus5MiniDan]
GRANT EXECUTE ON RepairEstimate TO [Canopus5MiniDan]
EXEC sp_reserve_http_namespace
N'http://*:80/Repairs/req'
CREATE ENDPOINT RepairPoint
STATE = Started
AS HTTP
(
PATH = '/Repairs/req',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetEstimate'
(NAME = 'Repairs.dbo.RepairEstimate',
SCHEMA=STANDARD),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)
use master
GRANT CONNECT ON ENDPOINT::RepairPoint
TO [Canopus5MiniDan]
The batch in Listing 12-2 starts by creating a database named Repairs and then makes it the database in use.
Just because we are using HTTP doesn’t mean security goes out the window, so to speak. In this example, we are going to use Windows security to authenticate the use of our Web Service. That will make this Web Service the equivalent of what we would have if we used Windows authentication in SQL Server for TDS connections. So a client using this Web Service is no more insecure or dangerous than a client that connects to SQL Server using TDS and Windows authentication. In fact, you actually have greater control over an HTTP endpoint than TDS; HTTP endpoints can be individually enabled, and an identity connecting to the HTTP endpoint must have connect permission for that endpoint, so access to it can be managed in the same way that access to any other database object is.
Because we are going to use Windows authentication, we will need a Windows user to access our Web Service. We gave the Windows user MiniDan a SQL Windows login for our test. MiniDan is from the Canopus5 domain.
The implementation of RepairEstimate follows. Note that RepairEstimate is a bogus function, in that it always returns 12. We are going to expose RepairEstimate through a Web Service, but this of itself does not give anyone the right to execute it or to use any of the objects in SQL Server 2005. A user executing a Web Service needs the same permission that he would if he connected via TDS and tried to execute the underlying function or stored procedure.
We create a Canopus5MiniDan user in the Repair database and then grant that user execute permission on RepairEstimate. We might have made Canopus5MiniDan a member of a role that had execute permission or a member of a group that owned the schema for RepairEstimate. The important point is that we manage permissions for a user of Web Services in exactly the same way that we do for users who connect with TDS.
A Web Service lives at a Web address. In SQLXML 3.0, we used either the COM object model or the MMC snap-in to create a virtual directory in IIS for our Web Service. HTTP commands sent to this virtual directory would be directed to our Web Service. SQL Server 2005 uses http.sys to accomplish the same thing.
The platform SDK provides an API for the http.sys driver. This API allows an application to say, in effect, “From now on, listen for HTTP requests that arrive for ‘http://canopus5:80/Repairs/req’ and send them to me.” The CREATE ENDPOINT expression uses this API under the covers to listen on the appropriate URL.
Access to http.sys is controlled on a by-URL basis. A Windows administrator can access any URL, but in general, other Windows identities must be registered in advance. The http.sys driver maintains a list of registered URLs; each URL is associated with an ACL, which is an access control token. The ACL is in effect a list of Windows identities that are allowed to listen on that URL. Whenever an application attempts to use http.sys to listen on a URL, the identity of the user running the application is checked against the ACL. If that user identity or the identity of a group the user belongs to is not in the ACL, the application will not be allowed to listen on that URL.
For SQL Server 2005, this means that the identity that SQL Server 2005 runs under will be checked against the ACL for any URL used in a CREATE ENDPOINT expression for an HTTP endpoint. If SQL Server 2005 is running as the local system, it will have access to all possible URLs. If it is running under any other identity, the identity probably will have to have the identity registered in advance of using CREATE ENDPOINT.
SQL Server 2005 does not provide a way to see what URLs already have identities registered. If an identity is already registered for SQL Server 2005, it is unlikely that that URL will be available for use by CREATE ENDPOINT. There is a utility, httpcfg.exe, that is part of the Windows XP Support Tools. These tools can be found in the support ools directory of either the Windows Server 2003 or Windows XP Professional SP2 distribution.
We would like our Web Service to have http://Canopus5:80/Repairs/req for a URL. Figure 12-1 shows the httpcfg utility being used to find the HTTP endpoints that are already registered. We can see the three endpoints on port 80 that are assigned an ACL, and none of them uses the URL we want, so the URL http://Canopus5:80/Repairs/req is available to us.
Figure 12-1. httpcfg
The httpcfg utility can also be used to reserve an endpoint, but it is easier to do that using T-SQL. The sp_reserve_http_namespace stored procedure can be used to reserve the URL. Figure 12-3, later in this chapter, shows the sp_reserve_http_namespace being used just before CREATE ENDPOINT to reserve an HTTP endpoint. Note that the CREATE ENDPOINT will itself attempt to reserve the namespace if necessary, but in some cases, this will fail, because the identity that uses the CREATE ENDPOINT expression does not have the permission required to register an endpoint.
Even in those cases where it is not required to reserve a URL, it is best practice to use sp_reserve_http_namespace just for clarity. A URL reservation can be removed by using the sp_delete_http_namepace_reservation.
Reservation and deletion of reservations of URL for http.sys may be done only by a Windows administrator. When sp_reserve_http_namespace and sp_delete_http_namespace_reservation are executed, they impersonate the current user. This means that the current user must be a Windows login and must be a Windows administrator, or the stored procedures will fail. Likewise, the stored procedures will fail if the user has an SQL Server login instead of a Windows login.
Create Endpoint
A CREATE ENDPOINT expression for an HTTP endpoint has two major sections. The first section defines the parameters associated with an HTTP endpoint, and the second section defines the Web Service being hosted on that endpoint.
Every endpoint has a name. Figure 12-2 shows the CREATE ENDPOINT expression for the endpoint named RepairPoint (1). Note that an endpoint is scoped to the instance of SQL Server 2005, not to a particular database, so there can be no other RepairPoint endpoint in this instance of SQL Server.
Figure 12-2. CREATE ENDPOINT
This endpoint is created in the Started (2) state, which is the default if it is not specified. We will discuss the STATE option in more detail later in this chapter.
SQL Server 2005 supports several endpoints, and the AS clause specifies the type. In this case, the endpoint is an AS HTTP (3) endpoint. The parameters for an HTTP endpoint are defined within the AS HTTP clause. This endpoint will use INTEGRATED (4) authentication, so it will operate in the same way as when a client using TDS connects to SQL Server 2005 using integrated security.
Endpoint URL
Parts of the AS HTTP clause are mapped into the parts of a URL, which is defined in the Uniform Resource Identifiers RFC (2396). This RFC breaks a URL into three parts: the scheme, the authority, and the path. The scheme for HTTP is “http://”. The authority is the part that follows the scheme, up to the first following /. The authority in this example is Canopus5:80. The authority typically is the name of the machine and port, or the domain name and port. What follows it is the path.
In Figure 12-3, the authority is made from the values of the SITE and PORTS options of the AS HTTP phrase. The PORTS option doesn’t specify a port, which means that port 80 should be used.
Figure 12-3. Mapping of AS HTTP to URL
An asterisk (*) for the SITE means that any HTTP requests for any authority—for port 80, in this case—will be handled by this endpoint. The HTTP protocol allows for multiple authorities to occupy the same IP address. In this example, however, the only authority being listened for is Canopus5:80.
The value of the PATH option is the path for the URL.
The URL that was created from the AS HTTP clause should be, and in some cases must be, reserved before the CREATE ENDPOINT expression is executed. The result of running httpcfg.exe after the sp_reserve_http_ namespace stored procedure is executed, as it is in Listing 12-2 earlier in this chapter, is shown in Figure 12-4. Here, we can see that http://Canopus5:80//Repairs/req has been reserved. Note that httpcfg.exe always shows the canonical form of the URL, no matter how it was reserved, so it will always contain a / at the end.
Figure 12-4. URL reserved by SQL Server
Endpoint State
It is important to note that only users who are members of the sysadmin role or who have been granted CREATE ENDPOINT permission can use a CREATE ENDPOINT expression. There are no HTTP endpoints defined by default. When you install a fresh version of SQL Server on a system, you have no HTTP connectivity. Someone with appropriate privileges must use CREATE ENDPOINT to make an HTTP endpoint.
An important property of an endpoint is that it has a state. The endpoint state can be set to STARTED, STOPPED, or DISABLED using a CREATE ENDPOINT or ALTER ENDPOINT expression.
When the state of an HTTP endpoint is STARTED, it is listening for HTTP requests and will process them as appropriate. When the state is STOPPED, the HTTP endpoint will return an error message, “HTTP status 503: Service Unavailable,” to all requests sent to that endpoint. When the state is DISABLED, the endpoint will not be visible to a client making an HTTP request—that is, the client will time out trying to connect to the endpoint. Note that when the state is changed from DISABLED to any other value, SQL Server 2005 must be restarted for the change to take effect. Other changes take effect immediately.
The Surface Area Configuration tool that ships with SQL Server 2005 also can be used to start and stop, but not disable, HTTP endpoints. HTTP endpoints are managed as a Surface Area Connection Feature. Figure 12-5 shows the Surface Area Configuration Tool. An HTTP endpoint used for a Web Service is called a Native XML Web Service feature (1). Figure 12-5 shows the RepairPoint (2) Web Service being stopped (3).
Figure 12-5. Surface area configuration tool
Endpoint Metadata
The data that defines an HTTP endpoint can be seen in two system views: sys.endpoints and sys.http_endpoints. Figure 12-6 shows a SELECT expression (1) that retrieves some of the metadata for the HTTP endpoints in this instance of SQL Server 2005. The views do duplicate some of the metadata but are divided into generic endpoint data (2) and endpoint data specific to HTTP (3). Roughly, the content of sys.http_endpoints comes from the information in the AS HTTP clause.
Figure 12-6. Endpoint metadata
You might notice that this query did not list the same HTTP endpoints shown in Figure 12-5 that illustrated the use of httpcfg. Those HTTP endpoints were not reserved by this instance of SQL Server 2005; they were reserved by some other application. The sys.endpoints and sys.http_endpoints views show only the endpoints created by CREATE ENDPOINT.
Webmethods
The first section of the CREATE ENDPOINT expression defines the characteristics of an HTTP endpoint in an AS HTTP clause. The second section is a FOR SOAP clause that defines the Webmethods available to this endpoint. The term Webmethod comes from ASP.NET and in ASP.NET means a .NET Framework method that is mapped to a Web Service. Likewise, in SQL Server 2005, a Webmethod maps a SQL Server function or stored procedure to an HTTP endpoint.
The FOR SOAP clause from the batch in Listing 12-2 earlier in this chapter is shown in Figure 12-7. It defines a single WEBMETHOD that maps the Repairs.dbo.RepairEstimate to the GetEstimate Webmethod. Note that the three-part name of the stored procedure or function is required. The database specified by the DATABASE phrase is the database that will be in use when the stored procedure or function is executed.
Figure 12-7. Mapping Webmethod to function
Most development environments make it very straightforward to use a Web Service. Visual Studio 2005 has an Add Web Reference wizard that makes using a Web Service no different from using any class from a .NET Framework assembly. Other development environments for Windows and other platforms have similar capabilities.
Web Services work by receiving, and possibly replying with, SOAP messages using HTTP POST commands. The SOAP message format is described in SOAP Version 1.2 Part 0: Primer, at http://www.w3.org/TR/2003/RECsoap12-part0-20030624/, and the Simple Object Access Protocol (SOAP) 1.1, located at http://www.w3.org/TR/2000/NOTE-SOAP-20000508/. It is beyond the scope of this book to discuss SOAP messages fully, but we will look at a few examples of them.
Note that HTTP supports numerous commands; GET and POST are the most common. The GET command is what a Web browser typically uses to browse a site. It includes just the URL for the site and possibly a query string but no other information. The POST command is similar but may also include a message body. Web browsers use the POST command to send a form to a Web site for processing, for example. Likewise, SOAP uses POST commands so that it can send its message in the body of the POST command.
The use of SOAP and HTTP by Web Services means that any application that can support both HTTP and XML can use a Web Service hosted in SQL Server 2005. In practice, most applications do not attempt to use HTTP or XML directly but instead use a tool that produces a proxy class that the application uses. Then, to the application, the Web Service appears as an object with methods that represent the Webmethods in the Web Service. In other words, the Web Service appears as just another class the program uses.
The Visual Studio 2005 Add Web Reference wizard will build a proxy for a Web Service. Figure 12-8 shows a console application in Visual Studio 2005 that is adding a Web reference so that it can use a Web Service. Appendix C contains an introduction to using Visual Studio 2005.
Figure 12-8. Visual studio adding Web Reference
The Add Web Reference wizard brings up a dialog box like the one in Figure 12-9. You fill in the URL for the Web Service, and the wizard builds a proxy class for the Web Service. The URL that it wants is the one that was reserved (1) for the CREATE ENDPOINT expression, but with a ?WSDL query string (2) appended. Under the covers, the Add Web Reference wizard is using an HTTP GET command in exactly the same way that a Web browser would. In effect, SQL Server is returning a Web page that Visual Studio 2005 reads to get the information it needs to create a proxy. Note that SQL Server 2005 Web Services are not required to support the ?WSDL query string, but doing so simplifies the development of applications that use that Web Service.
Figure 12-9. Visual studio making proxy
When the proxy has been generated by clicking the Add Reference button in the Add Web Reference dialog box, it can be used by the program. Figure 12-10 shows the proxy in a program. The wizard named the proxy class RepairPoint, which corresponds to the name of the endpoint that hosts the Web Service. The program makes an instance (1) of the RepairPoint class. This Web Service requires Windows authentication, so the credentials of the user running this program are added (2) to the RepairPoint object.
Figure 12-10. Using a proxy
Then the program uses the GetEstimate method (3) of the RepairPoint object to get an estimate of the repair cost for a B-990 model automobile. The result of running the program is shown in Figure 12-11 and, not surprisingly, is 12.
Figure 12-11. Estimate
To the developer of the application in Figure 12-10, the Web Service in SQL Server 2005 appears as just another class she used to write her code.
Note that the developer did not have to use ADO.NET or the SQL language to access SQL Server 2005.
Behind the scenes, the GetEstimate method of the Repair class is creating a SOAP message and using HTTP to send it to SQL Server 2005. The SOAP message generated by the GetEstimate method in Figure 12-10 is shown in Listing 12-3. This is a SOAP message. Every SOAP message has a Body element inside an Envelope element. The contents of the Body element are considered to be the payload of the message. Here, we can see that the payload of the message is the GetEstimate method call and parameter converted to XML.
Listing 12-3. SOAP request
<soap:Envelope xmlns:soap=
"http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<GetEstimate xmlns="urn:autos">
<model>B-990</model>
</GetEstimate>
</soap:Body>
</soap:Envelope>
What comes back as a response to the GetEstimate method is also a SOAP message and is shown in Listing 12-4. There is a lot more gobbledygook in the response than in the request; later in this chapter, we will see why that is. The payload, at the end of this message, is just the return value, 12, for the GetEstimate function converted to XML.
Listing 12-4. SOAP response
<SOAP-ENV:Envelope xml:space="preserve"
xmlns:xsd=
"http://www.w3.org/2001/XMLSchema"
xmlns:xsi=
"http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENV=
"http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql=
"http://schemas.microsoft.com/sqlserver/2004/SOAP"
xmlns:sqlsoaptypes=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types"
xmlns:sqlrowcount=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlRowCount"
xmlns:sqlmessage=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlMessage"
xmlns:sqlresultstream=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlResultStream"
xmlns:sqltransaction=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlTransaction"
xmlns:sqltypes=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:method="urn:autos">
<SOAP-ENV:Body>
<method:GetEstimateResponse>
<method:GetEstimateResult>12</method:GetEstimateResult>
</method:GetEstimateResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Sometimes, when working on a SQL Server 2005 application, you will use the SQL Profiler utility to watch what SQL Server is doing. To do a similar sort of thing when SQL Server 2005 is hosting a Web Service, you will need to monitor the HTTP commands and response going to and from SQL Server 2005. Numerous tools for doing this are available. One that is particularly useful is Fiddler, written by Eric Lawrence from Microsoft. It is available as a free download from http://www.fiddlertool.com.
WSDL Web Service Definition Language
As you can see, it is fairly easy for a developer using Visual Studio 2005 to build applications that use Web Services that are hosted in SQL Server 2005. What about developers using other development environments on other platforms?
In fact, virtually all development environments on all platforms in common use today have tools similar to the one in Visual Studio 2005 and are about as easy to use. All these development environments, including Visual Studio 2005, depend on being able to access the Web Service Definition Language (WSDL) file that defines the Web Service.
A WSDL file is an XML file that defines everything you need to know to access a Web Service. Several versions of WSDL are available. WSDL 1.1 is described in the WSDL 1.1 note of the World Wide Web Consortium at http://www.w3.org/TR/wsdl#_wsdl. WSDL 1.2 is described in the WSDL Version 1.2 working draft at http://www.w3.org/TR/2003/WD-wsdl12-20030303/. A new version of WSDL is being developed and is described in WSDL Version 2.0 working draft at http://www.w3.org/TR/wsdl20/. It is beyond the scope of this book to discuss WSDL files in detail, but we will cover some of the basics.
All these specifications are somewhat detailed because they are designed to accommodate a wide range of definitions of what is considered to be a Web Service. The later versions make it easier to be more specific and less ambiguous about what is in a Web Service and what kind of data it can consume and produce. WSDL 2.0, for example, probably will provide a standard way to manage versions of a Web Service.
In practice today, all development environments support WSDL 1.1, and some that support WSDL 1.2. SQL Server 2005 will automatically produce a WSDL 1.1 file based on the definition of the Web Service in the CREATE ENDPOINT expression. It does this in response to an HTTP GET command with a ?WSDL query string appended to the URL. The Add Web Reference wizard, shown in Figure 12-9 got back a WSDL file and then used the information in that WSDL to build a proxy class for the application.
The WSDL option in the FOR SOAP clause in Figure 12-7 specified that a DEFAULT WSDL file should be generated. This means that SQL Server 2005 will automatically create the WSDL file for this Web Service. It uses the system stored procedure sp_http_generate_wsdl_defaultcomplexorsimple to do this. We can see this in the metadata stored for a Web Service,
The information about Web Services can be seen in the sys.soap_endpoints system view. The information about the Webmethods in Web Services can be seen in the sys.endpoint_Webmethods view. Figure 12-12 shows a SELECT expression (1) that retrieves the wsdl_generator_procedure column from the sys.soap_endpoints view. You can see that the WSDL=DEFAULT option puts (2) the sp_http_generate_wsdl_defaultcomplexorsimple into the wsdl_generator_procedure column.
Figure 12-12. WSDL generator
When a Web Service receives an HTTP GET command, it invokes the wsdl_generator_procedure for the endpoint. This is how the Add Web Reference wizard in Visual Studio 2005, shown in Figure 12-9 earlier in this chapter, was able to get the WSDL file needed to create the proxy for the application. Listing 12-5 shows the WSDL file that SQL Server 2005 generated for the RepairPoint Web Service. As you can see, it contains quite a lot of information. Two key pieces of information it contains are the formats for the payloads of the SOAP messages that are used by the RepairPoint Web Service.
Listing 12-5. RepairPoint WSDL
<wsdl:definitions xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
xmlns:sqloptions="http://schemas.microsoft.com/sqlserver/2004/SOAP/Options"
xmlns:s0="urn:autos" xmlns:tns="urn:other"
targetNamespace="urn:other">
<wsdl:types>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
attributeFormDefault="qualified"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes">
<xsd:annotation>
<xsd:documentation xml:lang="en">
XML Schema describing
the base types to which SQL Server types are being mapped. For more
information, please consult the documentation.

(c)
Copyright 2004, Microsoft Corporation

The following
schema for Microsoft SQL Server is presented in XML format and is for
informational purposes only. Microsoft Corporation (“Microsoft”) may have
trademarks, copyrights, or other intellectual property rights covering
subject matter in the schema.

Microsoft does not make
any representation or warranty regarding the schema or any product or
item developed based on the schema. The schema is provided to you on an
AS IS basis. Microsoft disclaims all express, implied and statutory
warranties, including but not limited to the implied warranties of
merchantability, fitness for a particular purpose, and freedom from
infringement. Without limiting the generality of the foregoing, Microsoft
does not make any warranty of any kind that any item developed based on
the schema, or any portion of the schema, will not infringe any
copyright, patent, trade secret, or other intellectual property right of
any person or entity in any country. It is your responsibility to seek
licenses for such intellectual property rights where
appropriate.

MICROSOFT SHALL NOT BE LIABLE FOR ANY
DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION WITH THE USE OF THE
SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL (INCLUDING ANY LOST PROFITS), PUNITIVE OR SPECIAL DAMAGES,
WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF SUCH
DAMAGES.
</xsd:documentation>
</xsd:annotation>
<!-- Global types and attributes that can be used for schema
annotations. -->
<xsd:simpleType name="sqlDbTypeEnum">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="BigInt"/>
<xsd:enumeration value="Binary"/>
<xsd:enumeration value="Bit"/>
<xsd:enumeration value="Char"/>
<xsd:enumeration value="DateTime"/>
<xsd:enumeration value="Decimal"/>
<xsd:enumeration value="Float"/>
<xsd:enumeration value="Image"/>
<xsd:enumeration value="Int"/>
<xsd:enumeration value="Money"/>
<xsd:enumeration value="NChar"/>
<xsd:enumeration value="NText"/>
<!-- The sqlDbTypeEnum aligns with the .Net System.Data.SqlDbType
enum and does not provide an entry for Numeric (which is mapped to
Decimal). -->
<xsd:enumeration value="NVarChar"/>
<xsd:enumeration value="Real"/>
<xsd:enumeration value="SmallDateTime"/>
<xsd:enumeration value="SmallInt"/>
<xsd:enumeration value="SmallMoney"/>
<xsd:enumeration value="Text"/>
<xsd:enumeration value="Timestamp"/>
<xsd:enumeration value="TinyInt"/>
<xsd:enumeration value="Udt"/>
<xsd:enumeration value="UniqueIdentifier"/>
<xsd:enumeration value="VarBinary"/>
<xsd:enumeration value="VarChar"/>
<xsd:enumeration value="Variant"/>
<xsd:enumeration value="Xml"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="sqlCompareOptionsEnum">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="Default"/>
<xsd:enumeration value="None"/>
<xsd:enumeration value="IgnoreCase"/>
<xsd:enumeration value="IgnoreNonSpace"/>
<xsd:enumeration value="IgnoreKanaType"/>
<xsd:enumeration value="IgnoreWidth"/>
<xsd:enumeration value="BinarySort"/>
<xsd:enumeration value="BinarySort2"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="sqlCompareOptionsList">
<xsd:list itemType="sqltypes:sqlCompareOptionsEnum"/>
</xsd:simpleType>
<xsd:attribute default="NVarChar" name="sqlDbType"
type="sqltypes:sqlDbTypeEnum"/>
<xsd:attribute name="clrTypeName" type="xsd:string"/>
<xsd:attribute default="1" name="maxLength" type="xsd:long"/>
<xsd:attribute default="-1" name="localeId" type="xsd:int"/>
<xsd:attribute default="Default" name="sqlCompareOptions"
type="sqltypes:sqlCompareOptionsList"/>
<xsd:attribute default="0" name="sqlCollationVersion"
type="xsd:int"/>
<xsd:attribute default="0" name="sqlSortId" type="xsd:int"/>
<xsd:attribute default="0" name="scale" type="xsd:unsignedByte"/>
<xsd:attribute default="18" name="precision" type=
"xsd:unsignedByte"/>
<xsd:attribute name="xmlSchemaCollection" type="xsd:string"/>
<xsd:attribute name="sqlTypeAlias" type="xsd:string"/>
<!-- Global types that describe the base SQL types. -->
<xsd:simpleType name="char">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
<xsd:simpleType name="nchar">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
<xsd:simpleType name="varchar">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
<xsd:simpleType name="nvarchar">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
<xsd:simpleType name="text">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
<xsd:simpleType name="ntext">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
<xsd:simpleType name="varbinary">
<xsd:restriction base="xsd:base64Binary"/>
</xsd:simpleType>
<xsd:simpleType name="binary">
<xsd:restriction base="xsd:base64Binary"/>
</xsd:simpleType>
<xsd:simpleType name="image">
<xsd:restriction base="xsd:base64Binary"/>
</xsd:simpleType>
<xsd:simpleType name="timestamp">
<xsd:restriction base="xsd:base64Binary">
<xsd:maxLength value="8"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="timestampNumeric">
<!-- The timestampNumeric type supports a legacy format of
timestamp. -->
<xsd:restriction base="xsd:long"/>
</xsd:simpleType>
<xsd:simpleType name="decimal">
<xsd:restriction base="xsd:decimal"/>
</xsd:simpleType>
<xsd:simpleType name="numeric">
<xsd:restriction base="xsd:decimal"/>
</xsd:simpleType>
<xsd:simpleType name="bigint">
<xsd:restriction base="xsd:long"/>
</xsd:simpleType>
<xsd:simpleType name="int">
<xsd:restriction base="xsd:int"/>
</xsd:simpleType>
<xsd:simpleType name="smallint">
<xsd:restriction base="xsd:short"/>
</xsd:simpleType>
<xsd:simpleType name="tinyint">
<xsd:restriction base="xsd:unsignedByte"/>
</xsd:simpleType>
<xsd:simpleType name="bit">
<xsd:restriction base="xsd:boolean"/>
</xsd:simpleType>
<xsd:simpleType name="float">
<xsd:restriction base="xsd:double"/>
</xsd:simpleType>
<xsd:simpleType name="real">
<xsd:restriction base="xsd:float"/>
</xsd:simpleType>
<xsd:simpleType name="datetime">
<xsd:restriction base="xsd:dateTime">
<xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]
{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|
(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9]){2}(.[0-9]{2}[037])?"/>
<xsd:maxInclusive value="9999-12-31T23:59:59.997"/>
<xsd:minInclusive value="1753-01-01T00:00:00.000"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="smalldatetime">
<xsd:restriction base="xsd:dateTime">
<xsd:pattern value="((000[1-9])|(00[1-9][0-9])|(0[1-9][0-9]
{2})|([1-9][0-9]{3}))-((0[1-9])|(1[012]))-((0[1-9])|([12][0-9])|
(3[01]))T(([01][0-9])|(2[0-3]))(:[0-5][0-9])(:00)"/>
<xsd:maxInclusive value="2079-06-06T23:59:00"/>
<xsd:minInclusive value="1900-01-01T00:00:00"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="money">
<xsd:restriction base="xsd:decimal">
<xsd:totalDigits value="19"/>
<xsd:fractionDigits value="4"/>
<xsd:maxInclusive value="922337203685477.5807"/>
<xsd:minInclusive value="-922337203685477.5808"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="smallmoney">
<xsd:restriction base="xsd:decimal">
<xsd:totalDigits value="10"/>
<xsd:fractionDigits value="4"/>
<xsd:maxInclusive value="214748.3647"/>
<xsd:minInclusive value="-214748.3648"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:simpleType name="uniqueidentifier">
<xsd:restriction base="xsd:string">
<xsd:pattern value="([0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]
{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12})|({[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-
[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}})"/>
</xsd:restriction>
</xsd:simpleType>
<!-- sql_variant directly maps to xsd:anyType -->
<xsd:complexType name="xml" mixed="true">
<xsd:sequence>
<xsd:any minOccurs="0" maxOccurs="unbounded"
processContents="skip"/>
</xsd:sequence>
</xsd:complexType>
<!-- the following type is for FOR XML binary URL results only -->
<xsd:simpleType name="dbobject">
<xsd:restriction base="xsd:anyURI"/>
</xsd:simpleType>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified" elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/
SOAP/types">
<xsd:annotation>
<xsd:documentation xml:lang="en">
(c) Copyright 2004,
Microsoft Corporation

The following schema for
Microsoft SQL Server is presented in XML format and is for informational
purposes only. Microsoft Corporation ("Microsoft") may have trademarks,
copyrights, or other intellectual property rights covering subject matter
in the schema.

Microsoft does not make any representation
or warranty regarding the schema or any product or item developed based
on the schema. The schema is provided to you on an AS IS basis.
Microsoft disclaims all express, implied and statutory warranties,
including but not limited to the implied warranties of merchantability,
fitness for a particular purpose, and freedom from infringement. Without
limiting the generality of the foregoing, Microsoft does not make any
warranty of any kind that any item developed based on the schema, or any
portion of the schema, will not infringe any copyright, patent, trade
secret, or other intellectual property right of any person or entity in
any country. It is your responsibility to seek licenses for such intel-
lectual property rights where appropriate.

MICROSOFT
SHALL NOT BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN
CONNECTION WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY
DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS),
PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF
SUCH DAMAGES.
</xsd:documentation>
</xsd:annotation>
<xsd:complexType name="SqlRowSet">
<xsd:sequence maxOccurs="unbounded">
<xsd:element ref="xsd:schema"/>
<xsd:any/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="SqlXml" mixed="true">
<xsd:sequence>
<xsd:any processContents="skip"/>
</xsd:sequence>
</xsd:complexType>
<xsd:simpleType name="SqlResultCode">
<xsd:restriction base="xsd:int">
<xsd:minInclusive value="0"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:attribute name="IsDataSetWithSchema" type="xsd:boolean"/>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified" elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlTransaction">
<xsd:annotation>
<xsd:documentation xml:lang="en">
(c) Copyright 2004,
Microsoft Corporation

The following schema for
Microsoft SQL Server is presented in XML format and is for informational
purposes only. Microsoft Corporation ("Microsoft") may have trademarks,
copyrights, or other intellectual property rights covering subject matter
in the schema.

Microsoft does not make any representa-
tion or warranty regarding the schema or any product or item developed
based on the schema. The schema is provided to you on an AS IS basis.
Microsoft disclaims all express, implied and statutory warranties,
including but not limited to the implied warranties of merchantability,
fitness for a particular purpose, and freedom from infringement. Without
limiting the generality of the foregoing, Microsoft does not make any
warranty of any kind that any item developed based on the schema, or any
portion of the schema, will not infringe any copyright, patent, trade
secret, or other intellectual property right of any person or entity in any
country. It is your responsibility to seek licenses for such intellectual
property rights where appropriate.

MICROSOFT SHALL NOT
BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION
WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT,
INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS),
PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF
SUCH DAMAGES.
</xsd:documentation>
</xsd:annotation>
<xsd:complexType name="SqlTransaction">
<xsd:sequence minOccurs="1" maxOccurs="1">
<xsd:element name="Descriptor" type="xsd:base64Binary"/>
<xsd:element name="Type">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:enumeration value="Begin"/>
<xsd:enumeration value="Commit"/>
<xsd:enumeration value="Rollback"/>
<xsd:enumeration value="EnlistDTC"/>
<xsd:enumeration value="Defect"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified" elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlRowCount">
<xsd:annotation>
<xsd:documentation xml:lang="en">
(c) Copyright 2004,
Microsoft Corporation

The following schema for
Microsoft SQL Server is presented in XML format and is for informational
purposes only. Microsoft Corporation ("Microsoft") may have trademarks,
copyrights, or other intellectual property rights covering subject matter
in the schema.

Microsoft does not make any representation
or warranty regarding the schema or any product or item developed based
on the schema. The schema is provided to you on an AS IS basis. Microsoft
disclaims all express, implied and statutory warranties, including but
not limited to the implied warranties of merchantability, fitness for a
particular purpose, and freedom from infringement. Without limiting the
generality of the foregoing, Microsoft does not make any warranty of any
kind that any item developed based on the schema, or any portion of the
schema, will not infringe any copyright, patent, trade secret, or other
intellectual property right of any person or entity in any country. It is
your responsibility to seek licenses for such intellectual property
rights where appropriate.

MICROSOFT SHALL NOT BE
LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION WITH
THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT,
INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS),
PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF
SUCH DAMAGES.
</xsd:documentation>
</xsd:annotation>
<xsd:complexType name="SqlRowCount">
<xsd:sequence minOccurs="1" maxOccurs="1">
<xsd:element name="Count" type="xsd:long"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqlmessage="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlMessage" attributeFormDefault="qualified" elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlMessage">
<xsd:annotation>
<xsd:documentation xml:lang="en">
(c) Copyright 2004,
Microsoft Corporation

The following schema for
Microsoft SQL Server is presented in XML format and is for informational
purposes only. Microsoft Corporation ("Microsoft") may have trademarks,
copyrights, or other intellectual property rights covering subject matter
in the schema.

Microsoft does not make any representa-
tion or warranty regarding the schema or any product or item developed
based on the schema. The schema is provided to you on an AS IS basis.
Microsoft disclaims all express, implied and statutory warranties,
including but not limited to the implied warranties of merchantability,
fitness for a particular purpose, and freedom from infringement. Without
limiting the generality of the foregoing, Microsoft does not make any
warranty of any kind that any item developed based on the schema, or any
portion of the schema, will not infringe any copyright, patent, trade
secret, or other intellectual property right of any person or entity in any
country. It is your responsibility to seek licenses for such intellectual
property rights where appropriate.

MICROSOFT SHALL NOT
BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION
WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT,
INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS),
PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF
SUCH DAMAGES.
</xsd:documentation>
</xsd:annotation>
<xsd:simpleType name="nonNegativeInteger">
<xsd:restriction base="xsd:int">
<xsd:minInclusive value="0"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="SqlMessage">
<xsd:sequence minOccurs="1" maxOccurs="1">
<xsd:element name="Class" type="sqlmessage:
nonNegativeInteger"/>
<xsd:element name="LineNumber" type="sqlmessage:
nonNegativeInteger"/>
<xsd:element name="Message" type="xsd:string"/>
<xsd:element name="Number" type="sqlmessage:
nonNegativeInteger"/>
<xsd:element name="Procedure" type="xsd:string" minOccurs="0"/>
<xsd:element name="Server" type="xsd:string" minOccurs="0"/>
<xsd:element name="Source" type="xsd:string"/>
<xsd:element name="State" type="sqlmessage:
nonNegativeInteger"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:
sqlsoaptypes="http://schemas.microsoft.com/sqlserver/2004/SOAP/types"
xmlns:sqlmessage="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlMessage"
xmlns:sqlrowcount="http://schemas.microsoft.com/sqlserver/2004/SOAP/types
/SqlRowCount"
xmlns:sqltransaction="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlTransaction" attributeFormDefault="qualified"
elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlResultStream">
<xsd:annotation>
<xsd:documentation xml:lang="en">
(c) Copyright 2004,
Microsoft Corporation

The following schema for
Microsoft SQL Server is presented in XML format and is for informational
purposes only. Microsoft Corporation ("Microsoft") may have trademarks,
copyrights, or other intellectual property rights covering subject matter in
the schema.

Microsoft does not make any representation or
warranty regarding the schema or any product or item developed based on
the schema. The schema is provided to you on an AS IS basis. Microsoft
disclaims all express, implied and statutory warranties, including but
not limited to the implied warranties of merchantability, fitness for a
particular purpose, and freedom from infringement. Without limiting the
generality of the foregoing, Microsoft does not make any warranty of any
kind that any item developed based on the schema, or any portion of the
schema, will not infringe any copyright, patent, trade secret, or other
intellectual property right of any person or entity in any country. It is
your responsibility to seek licenses for such intellectual property
rights where appropriate.

MICROSOFT SHALL NOT BE
LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION WITH
THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT, INDI-
RECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS), PUNITIVE
OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF SUCH
DAMAGES.
</xsd:documentation>
</xsd:annotation>
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/types"/>
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlMessage"/>
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/
SOAP/types/SqlRowCount"/>
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlTransaction"/>
<xsd:complexType name="SqlResultStream">
<xsd:choice minOccurs="1" maxOccurs="unbounded">
<xsd:element name="SqlRowSet" type="sqlsoaptypes:SqlRowSet"/>
<xsd:element name="SqlXml" type="sqlsoaptypes:SqlXml"/>
<xsd:element name="SqlMessage" type="sqlmessage:SqlMessage"/>
<xsd:element name="SqlRowCount" type="sqlrowcount:
SqlRowCount"/>
<xsd:element name="SqlResultCode" type="sqlsoaptypes:
SqlResultCode"/>
<xsd:element name="SqlTransaction" type="sqltransaction:
SqlTransaction"/>
</xsd:choice>
</xsd:complexType>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified" elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/Options">
<xsd:annotation>
<xsd:documentation xml:lang="en">
(c) Copyright 2004,
Microsoft Corporation

The following schema for
Microsoft SQL Server is presented in XML format and is for informational
purposes only. Microsoft Corporation ("Microsoft") may have trademarks,
copyrights, or other intellectual property rights covering subject matter
in the schema.

Microsoft does not make any representa-
tion or warranty regarding the schema or any product or item developed
based on the schema. The schema is provided to you on an AS IS basis.
Microsoft disclaims all express, implied and statutory warranties,
including but not limited to the implied warranties of merchantability,
fitness for a particular purpose, and freedom from infringement. Without
limiting the generality of the foregoing, Microsoft does not make any
warranty of any kind that any item developed based on the schema, or any
portion of the schema, will not infringe any copyright, patent, trade
secret, or other intellectual property right of any person or entity in any
country. It is your responsibility to seek licenses for such intellectual
property rights where appropriate.

MICROSOFT SHALL NOT
BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION
WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT,
INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS),
PUNITIVE OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF
SUCH DAMAGES.
</xsd:documentation>
</xsd:annotation>
<xsd:element name="initialDatabase">
<xsd:annotation>
<xsd:documentation>Set initial database on login.</xsd:
documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="value" type="xsd:string"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The name of the initial database to
attach to.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="optional" default="false"
type="xsd:boolean" form="unqualified">
<xsd:annotation>
<xsd:documentation>Whether the initial database is optional
or not.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="filename" type="xsd:string"
form="unqualified">
<xsd:annotation>
<xsd:documentation>The filename of the database to attach
to.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="initialLanguage">
<xsd:annotation>
<xsd:documentation>Set initial language to set.</xsd:
documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="value" type="xsd:string"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The name of the initial language to
set.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="optional" default="false"
type="xsd:boolean" form="unqualified">
<xsd:annotation>
<xsd:documentation>Whether the initial language is optional
or not.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="environmentChangeNotifications">
<xsd:annotation>
<xsd:documentation>Receive environment change
notifications.</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="databaseChange" default="false"
type="xsd:boolean" form="unqualified">
<xsd:annotation>
<xsd:documentation>Receive notifications of database
changes.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="languageChange" default="false"
type="xsd:boolean" form="unqualified">
<xsd:annotation>
<xsd:documentation>Receive notifications of language
changes.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="transactionBoundary" default="false"
type="xsd:boolean" form="unqualified">
<xsd:annotation>
<xsd:documentation>Receive notifications of transaction
boundaries.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="applicationName">
<xsd:annotation>
<xsd:documentation>Set the application name for the
login.</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="value" type="xsd:string"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The application name to set for the
login.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="hostName">
<xsd:annotation>
<xsd:documentation>Set the host name for the login.</xsd:
documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="value" type="xsd:string" form="unqualified"
use="required">
<xsd:annotation>
<xsd:documentation>The host name to set for the
login.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="clientPID">
<xsd:annotation>
<xsd:documentation>Set the client process ID for the
login.</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="value" type="xsd:long"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The client process ID to set for the
login.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="clientNetworkID">
<xsd:annotation>
<xsd:documentation>Set the client network ID for the
login.</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="value" type="xsd:base64Binary"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The client network ID to set for the
login.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="clientInterface">
<xsd:annotation>
<xsd:documentation>Set the client interface for the
login.</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="value" type="xsd:string"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The client interface to set for the
login.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="notificationRequest">
<xsd:annotation>
<xsd:documentation>Requests query notifications for the
request.</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="notificationId" type="xsd:string"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The notification identifier.</xsd:
documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="deliveryService" type="xsd:string"
form="unqualified" use="required">
<xsd:annotation>
<xsd:documentation>The delivery service.</xsd:
documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="timeout" type="xsd:integer"
form="unqualified">
<xsd:annotation>
<xsd:documentation>The timeout value.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
<xsd:element name="sqlSession">
<xsd:annotation>
<xsd:documentation>SQL Server SOAP Session</xsd:documentation>
</xsd:annotation>
<xsd:complexType>
<xsd:attribute name="initiate" default="false"
type="xsd:boolean" form="unqualified">
<xsd:annotation>
<xsd:documentation>Set to 'true' to request to start a new
session.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="terminate" default="false"
type="xsd:boolean" form="unqualified">
<xsd:annotation>
<xsd:documentation>Set to 'true' to request to terminate an
existing session.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="sessionId" type="xsd:base64Binary"
form="unqualified">
<xsd:annotation>
<xsd:documentation>The ID of a session.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="timeout" type="xsd:int" form="unqualified">
<xsd:annotation>
<xsd:documentation>The timeout in seconds before the
session expires.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
<xsd:attribute name="transactionDescriptor"
type="xsd:base64Binary" form="unqualified">
<xsd:annotation>
<xsd:documentation>The descriptor of a transaction to
enlist to.</xsd:documentation>
</xsd:annotation>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:
sqlparameter="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlParameter"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlParameter" elementFormDefault="qualified" attributeFormDefault=
"qualified">
<xsd:annotation>
<xsd:documentation xml:lang="en">
(c) Copyright 2004,
Microsoft Corporation

The following schema for
Microsoft SQL Server is presented in XML format and is for informational
purposes only. Microsoft Corporation ("Microsoft") may have trademarks,
copyrights, or other intellectual property rights covering subject matter
in the schema.

Microsoft does not make any representa-
tion or warranty regarding the schema or any product or item developed
based on the schema. The schema is provided to you on an AS IS basis.
Microsoft disclaims all express, implied and statutory warranties,
including but not limited to the implied warranties of merchantability,
fitness for a particular purpose, and freedom from infringement. Without
limiting the generality of the foregoing, Microsoft does not make any
warranty of any kind that any item developed based on the schema, or any
portion of the schema, will not infringe any copyright, patent, trade
secret, or other intellectual property right of any person or entity in any
country. It is your responsibility to seek licenses for such intellectual
property rights where appropriate.

MICROSOFT SHALL NOT
BE LIABLE FOR ANY DAMAGES OF ANY KIND ARISING OUT OF OR IN CONNECTION
WITH THE USE OF THE SCHEMA, INCLUDING WITHOUT LIMITATION, ANY DIRECT,
INDIRECT, INCIDENTAL, CONSEQUENTIAL (INCLUDING ANY LOST PROFITS), PUNITIVE
OR SPECIAL DAMAGES, WHETHER OR NOT MICROSOFT HAS BEEN ADVISED OF SUCH
DAMAGES.
</xsd:documentation>
</xsd:annotation>
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"/>
<xsd:simpleType name="ParameterDirection">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="Input"/>
<xsd:enumeration value="InputOutput"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:complexType name="ArrayOfSqlParameter">
<xsd:sequence>
<xsd:element minOccurs="0" maxOccurs="unbounded"
name="SqlParameter" type="sqlparameter:SqlParameter"/>
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="SqlParameter">
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1" name="Value"
nillable="true"/>
</xsd:sequence>
<xsd:attribute name="name" type="xsd:string" use="required"
form="unqualified"/>
<xsd:attribute default="NVarChar" name="sqlDbType" type=
"sqltypes:sqlDbTypeEnum" use="optional" form="unqualified"/>
<xsd:attribute default="Input" name="direction" type=
"sqlparameter:ParameterDirection" use="optional" form="unqualified"/>
<xsd:attribute default="1" name="maxLength" type="xsd:long"
use="optional" form="unqualified"/>
<xsd:attribute default="18" name="precision" type="xsd:
unsignedByte" use="optional" form="unqualified"/>
<xsd:attribute default="0" name="scale" type="xsd:unsignedByte"
use="optional" form="unqualified"/>
<xsd:attribute default="" name="clrTypeName" type="xsd:string"
use="optional" form="unqualified"/>
<xsd:attribute default="Default" name="sqlCompareOptions"
type="sqltypes:sqlCompareOptionsList" use="optional" form="unqualified"/>
<xsd:attribute default="-1" name="localeId" type="xsd:int"
use="optional" form="unqualified"/>
<xsd:attribute default="0" name="sqlCollationVersion"
type="xsd:int" use="optional" form="unqualified"/>
<xsd:attribute default="0" name="sqlSortId" type="xsd:int"
use="optional" form="unqualified"/>
<xsd:attribute default="" name="xmlSchemaCollection"
type="xsd:string" use="optional" form="unqualified"/>
</xsd:complexType>
</xsd:schema>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified" elementFormDefault="qualified"
targetNamespace="urn:autos"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
xmlns:sqlsoaptypes="http://schemas.microsoft.com/sqlserver/2004/SOAP/ types"
xmlns:sqlrowcount="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlRowCount"
xmlns:sqlmessage="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlMessage"
xmlns:sqlresultstream="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlResultStream"
xmlns:sqlparameter="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlParameter">
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"/>
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlResultStream"/>
<xsd:element name="GetEstimate">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1" name="model"
nillable="true">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar"
sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase
IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="10"></xsd:maxLength>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="GetEstimateResponse">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1" name=
"GetEstimateResult" type="sqltypes:int" nillable="true"></xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
</wsdl:types>
<wsdl:message name="s0MsgGetEstimateSoapIn">
<wsdl:part name="parameters" element="s0:GetEstimate"></wsdl:part>
</wsdl:message>
<wsdl:message name="s0MsgGetEstimateSoapOut">
<wsdl:part name="parameters"
element="s0:GetEstimateResponse"></wsdl:part>
</wsdl:message>
<wsdl:portType name="RepairPointSoap">
<wsdl:operation name="GetEstimate">
<wsdl:input name="s0MsgGetEstimateSoapIn" message="tns:
s0MsgGetEstimateSoapIn"></wsdl:input>
<wsdl:output name="s0MsgGetEstimateSoapOut"
message="tns:s0MsgGetEstimateSoapOut"></wsdl:output>
</wsdl:operation>
</wsdl:portType>
<wsdl:binding name="RepairPointSoap" type="tns:RepairPointSoap">
<soap:binding transport="http://schemas.xmlsoap.org/soap/http"
style="document"/>
<wsdl:operation name="GetEstimate">
<soap:operation soapAction="urn:autosGetEstimate"
style="document"></soap:operation>
<wsdl:input name="s0MsgGetEstimateSoapIn">
<soap:body use="literal"/>
</wsdl:input>
<wsdl:output name="s0MsgGetEstimateSoapOut">
<soap:body use="literal"/>
</wsdl:output>
</wsdl:operation>
</wsdl:binding>
<wsdl:service name="RepairPoint">
<wsdl:port name="RepairPoint" binding="tns:RepairPointSoap">
<soap:address
location="http://canopus5/Repairs/req"></soap:address>
</wsdl:port>
</wsdl:service>
</wsdl:definitions>
Note that one way to see that actual WSDL for a Web Service hosted in SQL Server 2005 is to use Internet Explorer to access the endpoint instead of the Visual Studio 2005 Add Web Reference wizard, as shown in Figure 12-13.
Figure 12-13. Using Internet Explorer
The format of the payloads for SOAP messages is defined in terms of the XML Schema 1.0 recommendation. A primer for this recommendation is available at http://www.w3.org/TR/2004/REC-xmlschema-0-20041028/.
It is beyond the scope of this book to discuss XML Schema in detail, but we will look at some simple aspects of it.
Listing 12-3 earlier in this chapter showed the SOAP message that was sent to SQL Server 2005 when the application called the GetEstimate method of the RepairPoint object. The Add Web Reference wizard in Visual Studio 2005 figured out the format for the payload of that SOAP message from the WSDL file shown in Listing 12-5. Figure 12-14 shows a fragment of that file: that part that defines the GetEstimate element, which is the payload of the SOAP message. It says the payload should be an element named “GetEstimate” (1) and that it should contain an element named “model” (2).
Figure 12-14. GetEstimate schema
The content of the model element is defined by a restriction (3). Restriction is an element from XML Schema 1.0 that allows you to refine a type definition further. In this case, the restriction contains a maxLength element, which says that the string contained in the model may not have more than ten characters.
If you look closely, you will see what appears to be the metadata that SQL Server 2005 uses to define a string. In this case, it says that the content of the model element should be a NVARCHAR; then it goes on to list things like the locale for the string. What SQL Server 2005 has done is map the metadata it uses for the @model parameter of the RepairEstimate stored procedure to XML.
Visual Studio 2005 has some idea of how to interpret the fact that model (2) is a NVARCHAR; Visual Studio 2005 was designed with support of SQL Server 2005 in mind. It is unlikely that other platforms and development environments will be able to interpret nvarchar. As shown in Figure 12-15, however, another part of the WSDL file defines NVARCHAR (1) in terms of a standard XML schema type, string. All platforms understand how to interpret an XML Schema 1.0 string.
Figure 12-15. Definition of nvarchar
By default, SQL Server 2005 will define data types in terms of their SQL type and then redefine the SQL type in terms of standard XML Schema 1.0 types. In fact, most of the WSDL files shown in Listing 12-5 earlier in this chapter consist of definitions of SQL types in terms of standard XML Schema 1.0 types.
SQL Server 2005 can create a WSDL file that defines data types directly in standard XML Schema types. Three possible query strings can be used. The ?WSDL one (2) is shown in Figure 12-9, to obtain a WSDL file from SQL Server 2005, WSDL, WSDL Complex, and WSDL Simple. By default, WSDL and WSDL Complex will produce the WSDL file shown in Listing 12-5. WSDL Simple will produce a similar WSDL file except that data types will be defined directly with XML Schema types.
Figure 12-16 shows the fragment of the WSDL file produced when WSDL Simple is used, which defines the format of the model parameter of the GetEstimate Webmethod. You can see here that the content of model is defined as the standard XML Schema 1.0 type string without the level of indirection through a NVARCHAR, which itself is defined as a string, as shown in Figure 12-15.
Figure 12-16. Element for GetEstimate
The choice of ?WSDLComplex or ?WSDLSimple depends on the needs of the client that will be using the Web Service, as well as the platform and environment used to develop the client.
In the case of the Add Web Reference wizard in Visual Studio 2005, when ?WSDLComplex is used, the proxy class will use data types from the System.Data.SqlTypes namespace. These are the data types that represent the corresponding data types found in SQL Server 2005—that is, they can be null. Figure 12-17 shows the IntelliSense provided by Visual Studio 2005 for the GetEstimate method for an application that used ?WSDLComplex to build the RepairPoint proxy class. You can see here that the SqlInt32 type was used for the return value and that the SqlString type was used for the input parameter. The SqlTypes are discussed in Chapter 3.
Figure 12-17. Proxy for ?WSDLComplex
When ?WSDLSimple is used with the Add Web Reference wizard in Visual Studio 2005, the proxy class will use data types from the System namespace—in other words, data types commonly used in .NET Framework applications that do not use ADO.NET. In some cases, these will be value types.
CLR value types are discussed in Appendix A and in the MSDN documentation for Visual Studio 2005 for .NET Framework; look for “value types” in the index. In brief, they are basically the primitive data types used for numerics, such System.Int32 and int in C#.
Two choices are available for each value, or primitive, type in .NET Framework 2.0. Prior to .NET Framework 2.0, there was no way to represent a null value for a value type like a System.Int32, but .NET Framework 2.0 added support for nullable types through the System.Nullable<> template class. System.Nullable<Int32>, for example, is an Int32 that can represent a null. Whether a nullable type is in the proxy class is determined by information in the XML Schema that defines the message.
Nullable types are discussed in the MSDN documentation for Visual Studio 2005 for .NET Framework; look for “Nullable<T>” in the index.
Just as the WSDL file includes an XML Schema 1.0 definition for the format of messages sent to it, as shown in Figure 12-14, it includes one for the messages it sends back to the client. Figure 12-18 shows the XML Schema 1.0 definition for GetEstimateResponse, which is in the payload of the SOAP message sent back to the client. It says that the GetEstimateResponse element should contain a GetEstimateResult element that contains the return value. Note that the return type is sqltypes:int, which is redefined as an int from XML Schema 1.0, just as the nvarchar was redefined as a string.
Figure 12-18. GetEstimateResponse schema
We can see the information that the Add Web Reference wizard uses in the definition for GetEstimateResult, shown in Figure 12-18; it has an attribute nillable=“true”. As the name implies, it means that the GetEstimateResult element is nillable. The nillable=“true” attribute is added whether ?WSDLSimple or ?WSDLComplex is used, but later, we will look at creating a custom XML Schema where this might not be the case.
There is no "NULL" defined in XML Schema 1.0, as there is in most databases, but the nillable=“true” attribute says in effect that the element represents a value that comes from a database and might be NULL. INTs in SQL Server 2005 can be NULL, which is why the GetEstimateResult is attributed in this way. This is not the only way this might be done, but the underlying reasons for this choice involve a discussion of how an XML document is validated against an XML Schema and is not part of the scope of this book.
The nillable=“true” attribute allows a message like the one shown in Listing 12-6 to be returned to the client. Note that the GetEstimateResult element has no content—that is, it contains no number, as the message in Listing 12-4 did, but it does have an attribute xsi:nil=“1”, which typically would be interpreted as meaning the GetEstimateResult is NULL. If the XML Schema shown in Figure 12-18 did not have the nillable=“true” attribute, this message would be invalid according to that XML Schema definition.
In short, SQL Server 2005 adds the nillable=“true” attribute to data type definitions for both ?WSDLComplex and ?WSDLSimple to indicate that a value for one of these data types might be NULL. It also marks null values with a xsi:nil=“1” attribute so that clients recognize a null value.
Listing 12-6. Return of NULL value
<SOAP-ENV:Envelope xml:space="preserve"
xmlns:xsd=
"http://www.w3.org/2001/XMLSchema"
xmlns:xsi=
"http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENV=
"http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql=
"http://schemas.microsoft.com/sqlserver/2004/SOAP"
xmlns:sqlsoaptypes=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types"
xmlns:sqlrowcount=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlRowCount"
xmlns:sqlmessage=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlMessage"
xmlns:sqlresultstream=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlResultStream"
xmlns:sqltransaction=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/types/SqlTransaction"
xmlns:sqltypes=
"http://schemas.microsoft.com/sqlserver/2004/sqltypes"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:method="urn:autos">
<SOAP-ENV:Body>
<method:GetEstimateResponse>
<method:GetEstimateResult xsi:nil="1"/>
</method:GetEstimateResponse>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
The way client code makes use of nillable and xsi:nil depends on the platform and development environment that created the proxy code the client will use to access the Web Service. In this case, the Add Web Reference wizard in Visual Studio 2005 interprets these data types as nullable types when they are directly specified as XML Schema numeric types by ?WSDLSimple.
The result is that client code that uses a proxy made with ?WSDLSimple must use the nullable types provided by the .NET Framework instead of SqlTypes types, as was done for ?WSDLComplex shown in Figure 12-18. Figure 12-19 shows the IntelliSense for a proxy created using ?WSDLSimple. Note that the GetEstimate function returns a int? type. int? is an alias for Nullable<Int32>, which is a type in .NET Framework. Most languages provide a set of aliases for the nullable types in .NET Framework 2.0; for C#, a ? is added as a suffix to the non-nullable version of the type.
Figure 12-19. Proxy for ?WSDLSimple
In many cases, the .NET Framework will hide the difference between a nullable and a non-nullable type. In some cases, however, the exact nullable type must be used. If the code in Figure 12-19 were changed as follows, it would not compile because of a mismatch between the return type of GetEstimate and the type of i2:
int i2 = rp.GetEstimate("asdf");
Remember all the things people have said about supporting null values making programming more complicated? They're true!
XML Namespaces
Namespaces are part of both SOAP messages and WSDL files. Namespaces are defined in the Namespaces in XML Recommendation at http://www.w3. org/TR/2004/REC-xml-names11-20040204/. A full discussion of namespaces is beyond the scope of the book, but in simple terms, a namespace is used to prevent clashes between items that have the same name but different definitions. Two people might want to make a definition of what an account is, for example. The definitions will be different, because the accounts will be used in different environments. Each person would choose a namespace for the names for his items. One might choose the namespace urn:big-company-com:bookkeeping and the urn:small-company-com:bookkeeping. The account from the urn:big-company-com:bookkeeping would have a different definition from that from the urn:little-company-com:bookkeeping, even though they have the same name.
A WSDL file has a targetNamespace, and the schema for the payload of a SOAP message also has a targetNamespace. The targetNamespace for the WSDL file is defined by the NAMESPACE option FOR SOAP clause, and the targetNamespace for the payload of a SOAP message is defined in the WEBMETHOD clause.
Figure 12-20 shows a fragment of the FOR SOAP clause (1) from Listing 12-2 that defines the RepairPoint Web Service. The “urn:autos” in the WEBMETHOD clause is the targetNamespace for the payload of the SOAP message for the Webmethod, which means that it is the namespace of the GetEstimate element (3).
Figure 12-20. Namespaces
Figure 12-20 shows the beginning of the WSDL file for the RepairPoint Web Service. The complete file is shown in Listing 12-15 later in this chapter. The targetNamespace of the wsdl:definitions element (2) in Figure 12-20 is “urn:other”, which was defined in the NAMESPACE option of the FOR SOAP (1) clause for the RepairPoint Web Service.
WSDL Generation
Figure 12-12 earlier in this chapter showed the FOR SOAP clause using the WSDL=DEFAULT option, and all that this does is configure the Web Service to use the sp_http_generate_wsdl_defaultcomplexorsimple stored procedure to generate the WSDL file. There are two other possible values for the WSDL option: NONE and the name of a stored procedure.
The WSDL=NONE option prevents access to the WSDL file for the Web Service. This may be useful if the WSDL file is considered to be valuable intellectual property, and you want to ensure that it can be obtained only through some kind of business channel.
You can also use the WSDL option to specify a particular stored procedure to be used to generate the WSDL file. You might want a simple WSDL file to be generated when the ?WSDL query string is used instead of the complex one. SQL Server 2005 provides a stored procedure that will do this; it is named sp_http_generate_wsdl_defaultsimpleorcomplex. If you set the WSDL=’sp_http_generate_wsdl_defaultsimpleorcomplex’ for your WSDL option in the FOR SOAP clause, ?WSDL and ?WSDLSimple will return a simple WSDL file, and ?WSDLComplex will return the complex one.
In some cases, you will want to return a custom WSDL file of your own design. In Web Services, it usually is best to keep things as simple as possible, and even the WSDLSimple file that SQL Server 2005 produced for the GetEstimate contains definitions for many SQL types, even though it did not use them. Listing 12-7 shows a custom WSDL file for the RepairPoint Web Service that does not include any of the XML Schema definitions for SQL types.
Listing 12-7. Custom WSDL file
<wsdl:definitions xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:sqloptions=
"http://schemas.microsoft.com/sqlserver/2004/SOAP/Options"
xmlns:s0="urn:autos"
xmlns:tns="urn:other"
targetNamespace="urn:other">
<wsdl:types>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified"
elementFormDefault="qualified"
targetNamespace="urn:autos">
<xsd:element name="GetEstimate">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1"
name="model" type="xsd:string"
nillable="true">
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="GetEstimateResponse">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1"
name="GetEstimateResult" type="xsd:int"
nillable="true">
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
</wsdl:types>
<wsdl:message name="s0MsgGetEstimateSoapIn">
<wsdl:part name="parameters"
element="s0:GetEstimate">
</wsdl:part>
</wsdl:message>
<wsdl:message name="s0MsgGetEstimateSoapOut">
<wsdl:part name="parameters"
element="s0:GetEstimateResponse">
</wsdl:part>
</wsdl:message>
<wsdl:portType name="RepairPointSoap">
<wsdl:operation name="GetEstimate">
<wsdl:input name="s0MsgGetEstimateSoapIn"
message="tns:s0MsgGetEstimateSoapIn">
</wsdl:input>
<wsdl:output name="s0MsgGetEstimateSoapOut"
message="tns:s0MsgGetEstimateSoapOut">
</wsdl:output>
</wsdl:operation>
</wsdl:portType>
<wsdl:binding name="RepairPointSoap"
type="tns:RepairPointSoap">
<soap:binding
transport="http://schemas.xmlsoap.org/soap/http"
style="document"/>
<wsdl:operation name="GetEstimate">
<soap:operation soapAction="urn:autosGetEstimate"
style="document">
</soap:operation>
<wsdl:input name="s0MsgGetEstimateSoapIn">
<soap:body use="literal"/>
</wsdl:input>
<wsdl:output name="s0MsgGetEstimateSoapOut">
<soap:body use="literal"/>
</wsdl:output>
</wsdl:operation>
</wsdl:binding>
<wsdl:service name="RepairPoint">
<wsdl:port name="RepairPoint"
binding="tns:RepairPointSoap">
<soap:address location=
"http://canopus5/Repairs/req"></soap:address>
</wsdl:port>
</wsdl:service>
</wsdl:definitions>
You could deploy a custom WSDL file by setting the WSDL=NONE option in the FOR SOAP clause and putting the WSDL file up on a share somewhere or in a UDDI (Universal Description, Discover and Integration) server. A discussion of UDDI is beyond the scope of this book, but it is discussed in the MSDN documentation; look for “UDDI” in the index.
You can also write your own WSDL generator procedure and use the WSDL option in the FOR SOAP clause to configure SQL Server 2005 to use it.
A WSDL generator procedure is a stored procedure that returns a rowset that has a single row with a single column of type NVARCHAR(MAX) named [XML_F52E2B61-18A1-11d1-B105-00805F49916B]. Note that this column name is used to indicate that the column contains XML. The value of the column is the WSDL file. Listing 12-8 shows the signature of the stored procedure.
When SQL Server 2005 calls a WSDL generator procedure, the @endpointID parameter gets the key you can use to look up the endpoint in the sys.http_endpoints view. The @isSSL parameter is 0 if this is a clear port and 1 if it is an SSL port. The @host parameter has the name of the host—that is, the name of the machine hosting the Web Service. The @queryString contains the query string from the URL that was used to request the WSDL file. The @userAgent contains the user agent header from the HTTP GET command that was used to get the WSDL file.
Listing 12-8. WSDL generator signature
CREATE
PROCEDURE GenerateRepairWSDL
(@endpointID as int,
@isSSL as bit,
@host as nvarchar(256),
@queryString as nvarchar(256),
@userAgent as nvarchar(256)
)
You will not know several pieces of information at the time you write a WSDL generator procedure. One of these is the name of the machine that the Web Service is hosted on. The WSDL file in Listing 12-7 earlier in this chapter hard-coded the machine name to Canopus5. This Web Service may be deployed on another system. The result is that at least some of the WSDL file will have to be generated dynamically when the WSDL generator procedure is called. Later, we will see that this is easy to do using XQuery.
In spite of the simplicity of the WSDL file shown in Listing 12-7, some applications still will prefer to use the WSDLComplex or WSDLSimple form, so any WSDL generator procedure should preserve access to them. To do this, the Web server should accept an extra query string to specify that the minimal WSDL file be produced. The Web Service should accept a ?WSDLMinimal query string in addition to the ?WSDL, ?WSDLSimple, and ?WSDLComplex query strings. Note that the choice of ?WDSLMinimal was arbitrary; you can use anything you want.
One of the things we have not discussed is the fact that a Web Service may be hosted on a Secure Sockets Layer (SSL) port and then accessed using HTTPS instead of HTTP. We won’t be discussing the details of using SSL due to space considerations. The use of SSL, however, is configured in the AS HTTP clause of the CREATE ENDPOINT expression. The AS HTTP clause shown in Listing 12-9 shows a Web Service configured to accept requests both in the clear and with SSL. The port used for HTTP in the clear is different from the one that uses SSL, and the WSDL file will have to reflect this.
Listing 12-9. Web Service using SSL
CREATE ENDPOINT RepairPoint
STATE = Started
AS HTTP
(
PATH = '/Repairs/req',
PORTS = (CLEAR, SSL),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
A key problem in making a WSDL generator procedure is creating the proper URL from the WSDL generator procedure input parameters. When SQL Server 2005 calls a WSDL generator procedure, the @endpointID parameter specifies which endpoint the WDSL file must be created for. Some of the information we will need is in the sys.http_endpoints view and is shown in the results of the SELECT expression in Figure 12-21.
Figure 12-21. HTTP Endpoint information
Note that the sys.http_endpoints view lists both a clear_port and an ssl_port. The @isSSL parameter shown in Listing 12-8 earlier in this chapter will be 0 if the WSDL file should be generated for the clear port and 1 if it should be generated for the SSL port. The ssl_port in Figure 12-21 is 0 because the AS HTTP clause used to make the RepairPoint Web Service, shown in Figure 12-3 earlier in this chapter, does not specify the use of SSL.
The @host parameter shown in Listing 12-8 passes in the name of the host machine, and the @queryString passes in the query string from the URL that requested the WSDL file.
You can implement a WSDL generator procedure completely in a single stored procedure, but in general, several things can be reused, so the example that follows will factor the implementation in three parts. One part will compose the proper URL for the Web Service; one will compose the WSDL file itself; and the last will dispatch the generation of the WSDL to the appropriate stored procedure.
Listing 12-10 shows a helper function named GetWSDLUrl. This function composes the URL for the endpoint and returns it as an NVARCHAR. The @endpointID is used to look up the endpoint information in the sys.http_endpoints view. The @isSSL parameter is used to put the proper scheme at the beginning of the URL and select either the clear or ssl port. The @host parameter is the machine name.
Listing 12-10. URL function
CREATE FUNCTION GetWSDLUrl(
@endpointID as INT,
@host NVARCHAR(256),
@isSSL as bit
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @vdir NVARCHAR(MAX);
DECLARE @clear_port NVARCHAR(5);
DECLARE @ssl_port NVARCHAR(5);
DECLARE @url_path NVARCHAR(MAX);
SELECT @clear_port = CAST(clear_port AS NVARCHAR(5)),
@ssl_port = CAST(ssl_port AS NVARCHAR(5)), @url_path = url_path
FROM sys.http_endpoints WHERE endpoint_id = @endpointID
IF @isSSl = 0
SELECT @vdir ='http://' + @host + ':' + @clear_port + @url_path;
ELSE
SELECT @vdir ='https://' + @host + ':' + @ssl_port + @url_path;
RETURN @vdir;
END
Figure 12-22 shows how the GetWSDLUrl function composes the URL from an endpoint_id, a host, and an SSL indicator. The appropriate port and path from the sys.http_endpoints view are put together with the hostname passed into the GetWSDLUrl stored procedure.
Figure 12-22. Using URL function
Listing 12-11 shows a helper function, RepairWSDL, that will create a WSDL file for the RepairPoint Web Service. Most of the function is the literal WSDL file. This might be better stored in a table but is done this way to make the example complete in a single place. The function starts by using the GetWSDLUrl function, shown in Listing 12-10, to get the URL that should be in the WSLD file.
The literal WSDL file is set into the @wsdl variable. Near the end of the function, an XQuery modify instruction is used to insert the URL made by the GetWSDLUrl into the address attribute of the soap:address element. It returns the @wsdl variable as an NVARCHAR(MAX). See Chapter 10 for a discussion of XQuery and the modify instruction.
Listing 12-11. RepairWSDL function
CREATE FUNCTION RepairWSDL
(@endpointID as int,
@isSSL as bit,
@host as nvarchar(256)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @vdir NVARCHAR(MAX);
SELECT @vdir = dbo.GetWSDLUrl(@endpointID, @host, @isSSL);
DECLARE @wsdl XML;
SET @wsdl =
'<wsdl:definitions xmlns:tns="urn:other" xmlns:soap="http://schemas.
xmlsoap.org/wsdl/soap/" xmlns:s0="urn:autos" targetNamespace="urn:other"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
<wsdl:types>
<schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
attributeFormDefault="qualified" elementFormDefault="qualified"
targetNamespace="urn:autos" xmlns="http://www.w3.org/2001/XMLSchema">
<xsd:element name="GetEstimate">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1" name="model"
nillable="true">
<xsd:simpleType>
<xsd:restriction base="xsd:string">
<xsd:maxLength value="10" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="GetEstimateResponse">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1"
name="GetEstimateResult" nillable="true" type="xsd:int" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</schema>
</wsdl:types>
<wsdl:message name="s0MsgGetEstimateSoapIn">
<wsdl:part name="parameters" element="s0:GetEstimate" />
</wsdl:message>
<wsdl:message name="s0MsgGetEstimateSoapOut">
<wsdl:part name="parameters" element="s0:GetEstimateResponse" />
</wsdl:message>
<wsdl:portType name="RepairPointSoap">
<wsdl:operation name="GetEstimate">
<wsdl:input name="s0MsgGetEstimateSoapIn"
message="tns:s0MsgGetEstimateSoapIn" />
<wsdl:output name="s0MsgGetEstimateSoapOut"
message="tns:s0MsgGetEstimateSoapOut" />
</wsdl:operation>
</wsdl:portType>
<wsdl:binding name="RepairPointSoap" type="tns:RepairPointSoap">
<soap:binding transport="http://schemas.xmlsoap.org/soap/http" />
<wsdl:operation name="GetEstimate">
<soap:operation soapAction="urn:autosGetEstimate" style="document" />
<wsdl:input name="s0MsgGetEstimateSoapIn">
<soap:body use="literal" />
</wsdl:input>
<wsdl:output name="s0MsgGetEstimateSoapOut">
<soap:body use="literal" />
</wsdl:output>
</wsdl:operation>
</wsdl:binding>
<wsdl:service name="RepairPoint">
<wsdl:port name="RepairPoint" binding="tns:RepairPointSoap">
<soap:address location="url" />
</wsdl:port>
</wsdl:service>
</wsdl:definitions>'
SET @wsdl.modify('
declare namespace soap="http://schemas.xmlsoap.org/wsdl/soap/";
replace value of (//soap:address[1]/@location)[1] with
sql:variable("@vdir")'),
RETURN CAST(@wsdl AS NVARCHAR(MAX));
END
Now we have the pieces we need to make a WSDL generator procedure, GenerateRepairWSDL, shown in Listing 12-12. If the query string passed in via @queryString is ?WSDLMinimal, GenerateRepairWSDL uses the RepairWSDL function to make a single-row, single-column rowset that contains the WSDL. If the query string is not ?WSDLMinimal, it delegates the creation of the WSDL file to the sp_http_generate_wsdl_defaultcomplexorsimple stored procedure—the stored procedure that SQL Server 2005 uses to create WSDL files. Note that using this technique, you could, if necessary, support several formats for your WSDL file.
Note that the SELECT statement that returns the XML for the WSDL file is aliased to [XML_F52E2B61-18A1-11d1-B105-00805F49916B]. As mentioned earlier in this chapter, this must be the name of the column returned by the stored procedure that creates the WSDL file.
Listing 12-12. WSDL generator procedure
CREATE PROCEDURE GenerateRepairWSDL
(@endpointID as int,
@isSSL as bit,
@host as nvarchar(256),
@queryString as nvarchar(256),
@userAgent as nvarchar(256)
) AS
BEGIN
IF @queryString = 'WSDLMinimal'
BEGIN
SELECT dbo.RepairWSDL(@endpointID, @isSSl, @host) AS
[XML_F52E2B61-18A1-11d1-B105-00805F49916B];
RETURN
END
ELSE
BEGIN
EXEC sp_http_generate_wsdl_defaultcomplexorsimple
@endpointID, @isSSL, @host, @queryString, @userAgent;
RETURN
END
END
Listing 12-13 shows a FOR SOAP clause configured to use the GenerateRepairWSDL stored procedure to generate the WSDL file for a Web Service.
Listing 12-13. FOR SOAP configured to use custom WSDL
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetPersonel
(NAME = 'Repairs.dbo.Names', SCHEMA=STANDARD),
WSDL= 'Repairs.dbo.GenerateRepairWSDL',
SCHEMA=STANDARD,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)
Stored Procedure in Web Service
The RepairPoint Web Service we have been working with will look familiar to developers who have been using Web Services, but it will look a bit different to developers who have been using ADO.NET and connecting to SQL Server 2005 using TDS. It is common in ADO.NET-based applications to issue arbitrary SQL queries or to call stored procedures and collect the rowsets produced in a System.Data.DataSet. SQL Server 2005 Web Services support this programming idiom, albeit with a programming model that is a bit different from that in ADO.NET. Note that this programming model was also supported in SQLXML 3.0.
By default, the payload of the SOAP message returned by SQL Server 2005 Web Service is in effect, the same as what would be returned in TDS, but in the form of XML.
To illustrate this, we are going to host the stored procedure Test5, shown in Listing 12-14, in a SQL Server 2005 Web Service. The stored procedure has an output parameter, prints a message, raises an error, and does a SELECT that returns a rowset with two rows in it. In other words, it does a lot of things you might expect a stored procedure to do.
Listing 12-14. Test5 stored procedure
CREATE PROC Test5 (@data INT output)
AS
SET @data = 5
PRINT 'This is the Test5 message'
RAISERROR ('Test5 error', 10, 1)
SELECT * FROM
(SELECT 1 AS ID, 'Joe' AS NAME
UNION
SELECT 2 AS ID, 'Jane' AS NAME)
AS A
Listing 12-15 shows the CREATE ENDPOINT expression used to make a Web Service that has the Test5 stored procedure as a Webmethod. It is essentially the same as the CREATE ENDPOINT expression used to make the RepairPoint Web Service shown in Listing 12-2 earlier in this chapter, except that the Web method is GetTest5, and it executes the Test5 stored procedure.
Listing 12-15. Test Web Service
CREATE ENDPOINT Test5
STATE = Started
AS HTTP
(
PATH = '/Test5',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetTest5'
(NAME = 'Repairs.dbo.Test5', SCHEMA=DEFAULT),
WSDL=DEFAULT,
SCHEMA=STANDARD
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)
We saw in Figure 12-15 earlier in this chapter that a SQL Server 2005 Web Service defines SQL Server scalar types in terms of XML Schema 1.0 types; in that case, it defined an NVARCHAR as an XML Schema 1.0 string. Likewise, a SQL Server 2005 Web Service defines the data typically found in a TDS stream in terms of SQL XML 1.0 types.
A SQL Server 2005 Web Service defines an XML Schema type name, SqlResponseStream, as the type of the payload of the SOAP message it returns for a stored procedure or an arbitrary SQL query. A SqlResultStream contains a collection of elements representing the items you would expect to be returned from an SQL query. The elements are enumerated below:
SqlRowSet |
A rowset from a query |
SqlXml |
The xml from an FOR XML statement or other XML |
SqlMessage |
The result of a RAISERROR or a PRINT statement |
SqlRowCount |
The number of rows |
SqlResultCode |
The result code of a stored procedure |
Listing 12-16 shows the SOAP message that would be sent in response to a client that used the GetTest5 Webmethod.
Listing 12-16. SOAP message response for Test5
<SOAP-ENV:Envelope
xml:space="preserve"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="http://schemas.microsoft.com/sqlserver/2004/SOAP"
xmlns:sqlsoaptypes="http://schemas.microsoft.com/sqlserver/2004/SOAP/types"
xmlns:sqlrowcount="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlRowCount"
xmlns:sqlmessage="http://schemas.microsoft.com/sqlserver/2004/SOAP/types/
SqlMessage"
xmlns:sqlresultstream="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlResultStream"
xmlns:sqltransaction="http://schemas.microsoft.com/sqlserver/2004/SOAP/
types/SqlTransaction"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:method="urn:autos">
<SOAP-ENV:Body>
<method:GetTest5Response>
<method:GetTest5Result xmlns="">
<sqlresultstream:SqlRowCount xsi:type="sqlrowcount:SqlRowCount">
<sqlrowcount:Count>1</sqlrowcount:Count>
</sqlresultstream:SqlRowCount>
<sqlresultstream:SqlMessage xsi:type="sqlmessage:SqlMessage">
<sqlmessage:Class>0</sqlmessage:Class>
<sqlmessage:LineNumber>5</sqlmessage:LineNumber>
<sqlmessage:Message>This is the Test5 message</sqlmessage:
Message>
<sqlmessage:Number>0</sqlmessage:Number>
<sqlmessage:Procedure>Test5</sqlmessage:Procedure>
<sqlmessage:Server>CANOPUS5</sqlmessage:Server>
<sqlmessage:Source>Microsoft-SQL/9.0</sqlmessage:Source>
<sqlmessage:State>1</sqlmessage:State>
</sqlresultstream:SqlMessage>
<sqlresultstream:SqlMessage xsi:type="sqlmessage:SqlMessage">
<sqlmessage:Class>0</sqlmessage:Class>
<sqlmessage:LineNumber>6</sqlmessage:LineNumber>
<sqlmessage:Message>Test5 error</sqlmessage:Message>
<sqlmessage:Number>50000</sqlmessage:Number>
<sqlmessage:Procedure>Test5</sqlmessage:Procedure>
<sqlmessage:Server>CANOPUS5</sqlmessage:Server>
<sqlmessage:Source>Microsoft-SQL/9.0</sqlmessage:Source>
<sqlmessage:State>1</sqlmessage:State>
</sqlresultstream:SqlMessage>
<sqlresultstream:SqlRowSet
xsi:type="sqlsoaptypes:SqlRowSet"
msdata:UseDataSetSchemaOnly="true"
msdata:UDTColumnValueWrapped="true">
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes">
<xsd:simpleType name="int">
<xsd:restriction base="xsd:int"/>
</xsd:simpleType>
<xsd:simpleType name="varchar">
<xsd:restriction base="xsd:string"/>
</xsd:simpleType>
</xsd:schema>
<xsd:schema
targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1"
xmlns="" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"
elementFormDefault="qualified">
<xsd:import
namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"/>
<xsd:element
name="SqlRowSet1"
msdata:IsDataSet="true"
msdata:DataSetNamespace="urn:schemas-microsoft-com:sql:
SqlDataSet"
msdata:DataSetName="SqlDataSet">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="row" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ID" type="sqltypes:int"/>
<xsd:element name="NAME">
<xsd:simpleType>
<xsd:restriction
base="sqltypes:varchar"
sqltypes:localeId="1033"
sqltypes:sqlCompareOptions="IgnoreCase
IgnoreKanaType IgnoreWidth"
sqltypes:sqlSortId="52">
<xsd:maxLength value="4"/>
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<diffgr:diffgram
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<SqlRowSet1
xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
<row>
<ID>1</ID>
<NAME>Joe</NAME>
</row>
<row>
<ID>2</ID>
<NAME>Jane</NAME>
</row>
</SqlRowSet1>
</diffgr:diffgram>
</sqlresultstream:SqlRowSet>
<sqlresultstream:SqlRowCount
xsi:type="sqlrowcount:SqlRowCount">
<sqlrowcount:Count>2</sqlrowcount:Count>
</sqlresultstream:SqlRowCount>
<sqlresultstream:SqlResultCode
xsi:type="sqlsoaptypes:SqlResultCode">0</sqlresultstream:
SqlResultCode>
</method:GetTest5Result>
<method:data>5</method:data>
</method:GetTest5Response>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
Figure 12-23 reduces the SOAP message shown in Listing 12-16 to its bare minimum and shows the correspondence of its elements to the Test5 stored procedure that produced it. The first thing it returns is a SqlRowCount with a value of 1, which indicates that the output parameter was set. Next come two SqlMessages: one for the PRINT statement and one for the error that was raised. Next comes a SqlRowSet that represents the rowset returned by the SELECT statement, followed by another SqlRowCount with a value of 2, for the two rows in the rowset. Next comes the SqlResultCode, with a value of 0, which is the result code returned by the stored procedure. Last is the value of the data output parameter, 5. If you have worked on the client side with ADO.NET, you know that output parameters always come at the end of the TDS stream.
Figure 12-23. SqlResultStream
Listing 12-17 shows a C# console program that processes the SqlResultStream returned by the Test5 Webmethod. It was created with Visual Studio 2005 and used the Add Web Reference wizard, via http://canopus5/Test5?WSDL, to make the Test5 proxy class used to access the Web Service. Note that canopus5 is the machine hosting SQL Server 2005.
A Webmethod that is implemented by a stored procedure returns an array of objects. Each object in the array is an object from the SqlResultStream returned by the Web Service. The beginning of the message in Listing 12-16 has XML Schema 1.0 type definitions for a SqlRowCount, SqlMessage, and a SqlResultCode. The Add Web Reference wizard uses these definitions to create .NET Framework classes with corresponding names. All these classes, along with the proxy class, Test5, are in the Test5Client.canopus5 namespace, and they are used in a .NET Framework program to represent the corresponding elements from the SqlResultStream.
In Figure 12-23, one of the things in the SqlResultStream is a SqlRowSet that represents the result of a query. The Add Web Reference wizard does not build a .NET Framework class for the SqlResultStream. The format of a SqlRowSet is something that can be read directly by a System.Data.DataSet. As a result, the SqlRowSet is returned as a DataSet.
A discussion of the System.Data.DataSet class is beyond the scope of this book, but in simple terms, it is a container of tables, and although it isn’t, it can be thought of as an in-memory database. DataSets are documented in the MSDN documentation for .NET Framework.
So the array of objects that is returned by the GetTest5 method is the TDS stream from SQL Server 2005 that the Web Service turned into XML and that the Add Web Reference wizard turned into objects. The program in Listing 12-17 processes the objects by checking the type of each one and then printing out a short description of what it is and its value.
Listing 12-17. SqlResultStream client program
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using System.Data;
using Test5Client.canopus5;
class Program
{
static void Main(string[] args)
{
canopus5.Test5 test5 =
new Test5Client.canopus5.Test5();
test5.UseDefaultCredentials = true;
SqlInt32 data = 0;
Object[] results = test5.GetTest5(ref data);
foreach (object result in results)
{
if (result is Int32)
{
Console.WriteLine("Result code {0}", (Int32)result);
}
if (result is SqlRowCount)
{
Console.WriteLine(
"Row count {0}",
(result as SqlRowCount).Count);
}
if (result is SqlMessage)
{
Console.WriteLine(
"Message {0}",
(result as SqlMessage).Message);
}
if (result is DataSet)
{
Console.WriteLine("DataSet");
(result as DataSet).WriteXml(Console.Out);
Console.WriteLine();
}
}
}
}
Figure 12-24 shows the results produced by the program in Listing 12-17.
Figure 12-24. Result of processing SqlResultStream
In some cases, the only things of interest in the SqlResultStream are the SqlRowSets it contains, and things like SqlRowCounts and SqlMessages can be ignored. The FORMAT option of the WEBMETHOD clause can be used to restrict the SqlResultStream to returning only SqlRowSets. There are two possible values for the FORMAT option: ALL_RESULTS and ROWSETS_ONLY. ALL_RESULTS is the default and produces the results in Listing 12-16. ROWSETS_ONLY restricts the SqlResultStream to SqlRowSets. Listing 12-18 shows the FOR SOAP clause configured for ROWSETS_ONLY.
Listing 12-18. ROWSETS_ONLY
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetTest5'
(NAME = 'Repairs.dbo.Test5',
SCHEMA=DEFAULT,
FORMAT=ROWSETS_ONLY),
WSDL=DEFAULT,
SCHEMA=STANDARD
DATABASE = 'Repairs',
NAMESPACE = 'urn:other'
)
When ROWSETS_ONLY is specified, the Webmethod returns a System.Data.DataSet instead of an array of objects. Listing 12-19 shows the GetTest5 method of the preceding example as it would be written if the Test5 Web Service were configured for ROWSETS_ONLY only.
Listing 12-19. DataSet return value
DataSet results;
results = test5.GetTest5(ref data);
SQL Batches
Setting FORMAT=ROWSETS_ONLY ignores all other results, such as a message and errors, that would be produced by the execution of the stored procedure associated with the Web Service. With ADO.NET, of course, an application cannot only call a stored procedure, but also execute an arbitrary SQL batch. SQL Server 2005 Web Services also can provide this capability, but it must be enabled in the Web Service. At first glance, it might seem to be folly to have a Web Service that allows arbitrary SQL to be executed. But keep in mind that the HTTP and Web Services are just alternatives to using TDS to connect to SQL Server 2005. All users are still authenticated and allowed to do only what they are authorized to do, just as is the case with a TDS connection.
Listing 12-20 shows a CREATE ENDPOINT expression that makes a Web Service named SQL Server that will accept arbitrary SQL as input. There are two possible values for the BATCHES option of the FOR SOAP clause: ENABLED and DISABLED. DISABLED is the default. When BATCHES = ENABLED, the Web Service will accept arbitrary SQL.
Listing 12-20. Batch-Enabled Web Service
CREATE ENDPOINT SQLServer
STATE = Started
AS HTTP
(
PATH = '/SQL',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE = 'Repairs',
NAMESPACE = 'urn:other',
BATCHES=ENABLED
)
Adding BATCHED=ENABLED to the CREATE ENDPOINT expression creates a Webmethod named sqlbatch. Its definition is in the WSDL file that SQL Server 2005 creates for the Web Service. This WSDL is similar to the one for the RepairPoint Web Service, shown in Listing 12-5, in that it begins with the XML Schema 1.0 type definition for the data types that SQL Server uses. The main difference is in the XML Schema 1.0 that defines the payload of the messages.
Listing 12-21 shows the part of the WSDL that defines the payload of the SOAP messages sent to and from the SQL Server Web Service. The sqlbatch element is used to send a command to a Web Service and contains two other elements: BatchCommands and Parameters. BatchCommands, as its name implies, is used to send a SQL batch to the Web Service. The parameters for the batch are in the Parameters element. If you are used to working the service side of things, this is sort of like using sp_executesql. If you are used to working on the client side of things, this is very similar to the ADO.NET System.Data.SqlClient.SqlCommand class.
The second element in the sqlBatchResponse is used to send results back to the client. It also contains two elements. The first is a SqlResultStream—the same thing that was used in the previous example Web Service, Test5, to return the results of a stored procedure. As was the case in Test5 Web Service example, the SqlResultStream will be returned as a DataSet. It also contains a Parameters element that is used to return any output parameters.
Listing 12-21. sqlbatch payload formats
<xsd:element name="sqlbatch">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1"
name="BatchCommands" type="xsd:string"/>
<xsd:element minOccurs="0" maxOccurs="1"
name="Parameters"
type="sqlparameter:ArrayOfSqlParameter"
nillable="true"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="sqlbatchResponse">
<xsd:complexType>
<xsd:sequence>
<xsd:element minOccurs="1" maxOccurs="1"
name="sqlbatchResult"
type="sqlresultstream:SqlResultStream"
nillable="false"/>
<xsd:element minOccurs="0" maxOccurs="1"
name="Parameters"
type="sqlparameter:ArrayOfSqlParameter"
nillable="true"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Listing 12-22 shows a C# console program that processes the sqlBatchResponse returned by sending a SQL batch to the SQL Server Web Services. It was created with Visual Studio 2005 and used the Add Web Reference wizard, via http://canopus5/SQLServer?WSDL, to make a proxy SQLServer class used to access the Web Service. Note that canopus5 is the machine hosting SQL Server 2005.
The proxy class generated by the Add Web Reference wizard, SQLServer, has a method named sqlbatch. It takes two parameters. The first is a string, which is the SQL batch. The second is a SqlParameter array.
Note that this SqlParameter class was generated by the Add Web Reference wizard, is not the same as the System.Data.SqlClient. SqlParameter class, and may cause name ambiguities that will prevent a program from compiling. There are various techniques to get rid of ambiguous names; the program in Listing 12-22 aliases the System.Data namespace because the program needs only the DataSet from it.
Listing 12-22. BATCHES=ENABLED client
using System;
using System.Collections.Generic;
using System.Text;
using ConsoleApplication2.canopus5;
using d=System.Data;
class Program
{
static void Main(string[] args)
{
SQLServer s = new SQLServer();
s.UseDefaultCredentials = true;
SqlParameter[] parameters = new SqlParameter[2];
parameters[0] = new SqlParameter();
parameters[0].direction = ParameterDirection.Input;
parameters[0].Value = 3;
parameters[0].name = "P1";
parameters[0].sqlDbType = sqlDbTypeEnum.Int;
parameters[1] = new SqlParameter();
parameters[1].direction = ParameterDirection.Input;
parameters[1].Value = 4;
parameters[1].name = "P2";
parameters[1].sqlDbType = sqlDbTypeEnum.Int;
object[] results = s.sqlbatch(
@"Select @P1 AS FIRST;
PRINT 'hello';
SELECT @P2 AS SECOND;", ref parameters);
foreach (object result in results)
{
if (result is SqlRowCount)
{
Console.WriteLine(
"Row count {0}",
(result as SqlRowCount).Count);
}
if (result is SqlMessage)
{
Console.WriteLine(
"Message {0}",
(result as SqlMessage).Message);
}
if (result is d.DataSet)
{
Console.WriteLine("DataSet");
(result as d.DataSet).WriteXml(Console.Out);
Console.WriteLine();
}
}
}
}
Figure 12-25 shows the results produced by the program in Listing 12-22.
Figure 12-25. Result of processing sqlBatchResponse
We have looked at three different Web Services: RepairPoint, which hosted a SQL Server 2005 user-defined function; Test5, which hosted a SQL Server 2005 stored procedure; and SQLServer, which hosted the sqlbatch Web method. Each was done in a separate Web Service just for simplicity; there is no requirement to do this. A single Web Service in SQL Server 2005 can host as many Webmethods as you want; just include a WEBMETHOD clause for each one in the FOR SOAP clause. Likewise, the FOR SOAP clause may have BATCHES=ENABLED even when it includes several WEBMETHOD clauses.
Other Features
There are several other features of Web Services in SQL Server 2005 to mention, but we will not discuss them in detail because of space limitations.
All the Web Services we have looked at so far have used Windows authentication. If your clients and/or middle-tier servers are using Windows authentication, there will be no issue in having them use it to use SQL Server 2005 Web Services.
When SQL Server 2005 is hosting a Web Service, it is acting as a Web server, just like IIS. Web servers can be configured to allow unauthenticated clients to use them; the client is said to be anonymous in this case, and the Web server provides an identity for a client that accesses it this way. SQL Server 2005 does not allow anonymous clients for Web Services—ever.
Web Services also support HTTP basic authentication. With basic authentication, a client sends a name and a password to the server, and the server uses that to authenticate the client. SQL Server 2005 will allow only basic authentication to be used if the connection is made using SSL. When basic authentication is used in conjunction with SSL, with an https:// scheme in the URL, the password is passed to the server in an encrypted form, so its confidentiality is preserved. When basic authentication is used, the name and password are authenticated against a Windows identity.
For SQL Server to use SSL, it must be configured with an SSL certificate. The httpcfg.exe utility mentioned earlier in this chapter, and shown being used to find registered URLs in Figure 12-1, is used to do this. The SSL topic in Books Online discusses how this is done.
Web Services, like Web servers, support the concept of a session. HTTP is a sessionless protocol, but by various conventions, it can support a session—that is, a series of requests from a specific client that saves state between requests. Sessions are not enabled by default; the SESSIONS clause in the CREATE ENDPOINT and ALTER ENDPOINT must be used to enable sessions. Likewise, the SESSION_TIMEOUT clause is used to set the timeout for a session—the maximum amount of time allowed between SOAP messages in the same session. Search for “Working with SOAP Sessions” in MSDN for more discussion of using sessions in Web Services.
SQL Server 2005 can also use a SQL Server login to authenticate a Web Service client. The WS-Security specification, which is maintained by OASIS (http://www.oasis-open.org), specifies a format for including a name and password as part of a SOAP message. SQL Server 2005 supports this format, provided that the server is configured for MIXED authentication—that is, the server supports both Windows and SQL Server authentication, and the connection is made using HTTPS. The WS-Security specification specifies ways to send a password in an encrypted form, but SQL Server 2005 does not support this. If HTTPS is used, however, the entire message will be encrypted by HTTPS. Search MSDN for “SQL Server Authentication over SOAP” for a discussion of using SQL authentication with Web Services.
SQLXML 4.0 Functionality and SQL Server 2005
SQLXML is a term that was used even before SQL Server 2000 to refer to XML functions usable with SQL Server. Some of the SQLXML functionality executes code inside the SQL Server process; some of it executes out of process, using middle-tier or client code through the OLE DB provider paradigm. As an example, “SELECT...FOR XML” queries transform relational rowsets into XML; the transformation can happen in OLE DB or in the server process itself.
After the release of SQL Server 2000, additional client-side/OLE DB functionality for using XML with SQL Server was provided through a series of Web releases. These were released via Microsoft’s Web site and officially supported. Soon after SQL Server 2000’s release, SQLXML Web Release 1.0 appeared on the scene, followed shortly thereafter by Web Release 2.0 and 3.0. Each release added more ways to integrate XML with SQL Server using IIS and client-side functions. In this section, we’ll use the term SQLXML to refer to the out-of-process functions only. The total amount of functionality in SQLXML 3.0 Service Pack 3 (the current release) is impressive. The features included are
• A SQL XML Bulk Loader, using annotated XSD schemas to map XML to relational data.
• Support of legacy XDR-mapping schemas, including a program to convert annotated XDR to annotated XSD.
• A SQLXMLOLEDB provider—a OLE DB service provider
• Client-side XML processing (FOR XML on client).
• RAW, EXPLICIT, and NESTED modes.
• Client-side XSLT postprocessing built into the model.
• A query dialect to submit SQL queries embedded in XML elements, known as template queries.
• A way to submit XPath queries to SQL Server using mapping schemas and a variation of the template query dialect.
• A set of ADO.NET data classes that exposes the same functionality as SQLXMLOLEDB in .NET Framework.
• Template and XPath queries that also are available directly through the SQLOLEDB provider.
• Updategrams that use annotated XSD schemas to map XML to SQL and accomplish multiple, possibly transacted, updates against relational tables.
• Diffgrams that use XML-specific format to perform multiple updates. These are similar to the diffgrams generated by the .NET Framework DataSet.
• An ISAPI application that encapsulated that functionality. This application enables using the functionality through templates or specially constructed URLs. In addition, it allows stored-procedure or template-query output to be formatted into SOAP packets, allowing support for Web Services.
• A MMC snap-in to configure the ISAPI application.
In SQL Server 2005, some of the SQLXML features have been superseded by equivalent or better functionality inside the database itself. See the article “XML in Yukon: New Version Showcases Native XML Type and Advanced Data Handling,” by Bob Beauchemin, in MSDN magazine, for an early feature list, and “XML Support in Microsoft SQL Server 2005,” by Shankar Pal, Mark Fussel, and Irwin Dolobowsky, for a more recent update. The inclusion of a native XML data type in SQL Server 2005 means that SQL Server no longer has to stream XML to clients. The XML data type can be used as a column in an ordinary SQL rowset. We’ll have more to say about that later in this chapter. SQLXML 3.0 can still be used with a SQL Server 2005 database at the SQL Server 2000 level of XML functionality. But in addition, there is a SQLXML 4.0 feature, included with SQL Server 2005, that supports and updates most of the old functionality to work with the new data types, such as the native XML data type and the new large value types—that is, VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX). SQLXML 4.0 is used by SQL Server Notification Services, and this part of SQL Server 2005 benefits from the ability to support those new data types. Users of SQLXML 3.0 can upgrade their programs too.
How SQLXML Works
SQLXML provides similar functionality as in-database XML but it can work on the middle-tier or client. It can use an adjunct to the in-database functionality to distribute the XML processing. You use the SQLXMLOLEDB provider in the “provider=” operand of the connection string and use the underlying provider in the “data provider=” operand of the connection string. All the SQLXML functionality included in the .NET Framework managed wrapper works both in version 3.0 and 4.0 by making calls to OLE DB providers, using a slightly different connection string. The OLE DB provider for SQL Server 2000 and earlier releases is SQLOLEDB. In SQL Server 2005, this provider was superseded by the SQL Native Client OLE DB provider, SQLNCLI. To get full support for the new data types, the SQL Native Client must be used. The older OLE DB provider still works with the new database version; some features of SQLXML, however, such as client-side FOR XML, need SQLNCLI to work well with the XML data type. The service provider has also been updated for SQL Server 2005; the updated one is called SQLXMLOLEDB4. The connection strings look like this:
SQLXML3
SQLXMLOLEDB provider:
Provider=SQLXMLOLEDB;
Data Provider=SQLOLEDB;
Server=srv;database=pubs;
Integrated Security=SSPI
ADO.NET managed classes:
Provider=SQLOLEDB;Server=srv;
database=pubs;Integrated Security=SSPI
SQLXML4
SQLXMLOLEDB provider:
Provider=SQLXMLOLEDB.4.0;
Data Provider=SQLNCLI;Server=srv;
database=pubs;Integrated Security=SSPI
ADO.NET managed classes:
Provider=SQLNCLI;Server=srv;
database=pubs;Integrated Security=SSPI
What this means in terms of installation is that to use all features of all SQLXML 4.0 to their fullest, you must also be sure that the SQL Native Client feature and the new SQLXMLOLEDB 4.0 provider are also installed. You can check for SQL Native Client in the Add and Remove Programs Control Panel applet. You can check for all the OLE DB providers by using the Data Links API. To do this graphically, first create a file with an extension of .udl. Double-click that file to open the Data Links API applet. The installed providers are listed on the Providers tab. If you have all the appropriate providers, you’re on your way to being set up and ready to go.
The bulk load component works a little differently. The COM component itself has been updated, so there is a new version-dependent class, “SQLXMLBulkLoad.SQLXMLBulkload.4.0”. This component can still use the SQLOLEDB provider, so its connection-string property looks like this:
"provider=SQLOLEDB;data source=srv;
database=pubs;integrated security=SSPI"
Note that both the SQLXMLOLEDB provider and the SQLXMLBulk-Load component use version-dependent COM PROGIDs, so you can install and run SQLXML 3.0 and SQLXML 4.0 side by side.
XML Stream and XML Column
SQL Server returns data from SELECT statements in columns and rows, known in the various data access APIs as Rowsets, Recordsets, Resultsets, or DataReaders. Each column in a Rowset must be a data type, and that data type must be known to SQL Server. In SQL Server 2000, there was no built-in XML data type, but XML data could be returned from in-database SQLXML functionality. This included but was not confined to the output from SELECT ... FOR XML queries. Rather than specifying the column as data type NVARCHAR or NTEXT, SQL Server 2000 returned XML data through a different type of TDS packet: the stream. This stream can be easily integrated with ASP and ASP.NET because the Request and Response classes in each of these models use streams.
You’ve seen the stream, at least indirectly. When you issue a SELECT ... FOR XML query in Query Analyzer or SQL Server Management Studio, you appear to receive a one-column rowset. The column has an interesting name, ‘XML_F52E2B61-18A1-11d1-B105-00805F49916B’. This GUID is not a normal column name. It’s an indicator to the underlying protocol to send the output as a stream instead of a Rowset. The SQLOLEDB provider can consume this stream through a COM object that implements the interface IStream. ADO (classic) implemented a special Stream class to encapsulate this functionality. Both SQLOLEDB and SQLXMLOLEDB providers also can stream commands as input. In OLE DB, the ICommandStream interface is used; in ADO, there is a CommandStream property on the Command object. The CommandStream is the basis for support of an additional query dialect in SQL Server: SQLXML (which is SQL statements embedded in XML wrapper elements). You can hook up the ASP or ASP.NET Request stream to feed the CommandStream.
ADO.NET supports the stream in two ways. The “vanilla” SQL Server data provider, System.Data.SqlClient, has a special method, ExecuteXmlReader, that can consume the stream. The XmlReader produced by this method can be used to populate the DataSet. The SqlXml managed classes have complete support for streaming the output of SELECT...FOR XML and also for streaming input. These classes make the appropriate calls to the underlying OLE DB provider.
In SQL Server 2005, however, there is a native XML data type. This means that SQL Server 2005 can send out columns and rows, with one or more rows being DBTYPE_XML. The way you accomplish this is to use the TYPE qualifier on SELECT FOR XML calls, like this:
SELECT * FROM authors FOR XML AUTO, TYPE
Bear in mind that even with this support in the server, you must still use an up-level API (like ADO.NET 2.0) to consume the XML column in the client. In addition, the TYPE qualifier does not work with all the variations of FOR XML—namely, not with the FOR XML EXPLICIT syntax. Unless you write a stored procedure or user-defined function that takes an XML data type as an input parameter, there is no additional support for using an XML data type as a CommandStream.
Data Type Support and SQLXML Functionality
SQLXML 4.0 contains some bug fixes, but the major change is support of the new SQL Server 2005 data types. Client-side functionality in SQLXMLOLEDB 3.0 service provider doesn’t recognize the XML data type, and the SQLOLEDB provider does not even support user-defined types in binary format. Let’s illustrate this by using some simple tables and a ComplexNumber UDT that is similar to one of the SQL Server Engine Samples. The table definitions are shown in Listing 12-23.
Listing 12-23. Tables with XML and UDT columns
CREATE TABLE ctab (
id INT PRIMARY KEY,
cnum complexnumber)
GO
INSERT ctab VALUES(1, '100:200i')
INSERT ctab VALUES(2, '5:6i')
GO
CREATE TABLE xmltab (
id INT PRIMARY KEY,
thexml XML)
GO
INSERT xmltab VALUES(1, '<root><person name="bob"/></root>')
INSERT xmltab VALUES(2, '<root><person name="mary"/></root>')
GO
We’ll try out the functionality with a simple C# program. The program, shown in Listing 12-24, uses the Microsoft.Data.SqlXml library from either SQLXML 3.0 or SQLXML 4.0. Only the connection strings need be changed, as mentioned earlier.
Listing 12-24. Program using XML stream
using System;
using System.IO;
using Microsoft.Data.SqlXml;
public static void Main()
{
// See connection string above...
DoSqlXml(connString, "select * from ctab for xml nested");
DoSqlXml(connString, "select * from xmltab for xml nested");
}
public static void DoSqlXml(connString string, cmdText string)
{
SqlXmlCommand xc = new SqlXmlCommand(connString);
xc.ClientSideXml = true;
xc.CommandText = cmdText;
Stream s = xc.ExecuteStream();
s.Position = 0;
StreamReader sr = new StreamReader(s);
Console.WriteLine(sr.ReadToEnd());
}
Using the XML data type with SQLXML 3.0 yields a completely entitized string, as shown in Listing 12-25.
Listing 12-25. Entitized XML
<xmltab id="1" thexml="<root><person
name="bob"/></root>"/>
<xmltab id="2" thexml="<root><person
name="mary"/></root>"/>
This entitized string would be difficult if not impossible to consume in a
vanilla XML client. Using SQLXML 4.0 yields the familiar XML serialized
form.
<xmltab id="1">
<thexml><root><person name="bob"/></root></thexml></xmltab>
<xmltab id="2">
<thexml><root><person name="mary"/></root></thexml></xmltab>
With a UDT input the results with SQLXML 3.0/SQLOLEDB provider are even
more startling:
<ctab id="1" cnum="dbobject/ctab[@id='1']/@cnum"/>
<ctab id="2" cnum="dbobject/ctab[@id='2']/@cnum"/>
With SQLXML 4.0, we get the binary representation of the UDT, as shown
below. To get the XML form of the UDT, you must CAST or CONVERT to XML on
the SERVER.
<ctab id="1" cnum="01C05900000000000001C06900000000000000C20000"/>
<ctab id="2" cnum="01C01400000000000001C018000000000000002C0000"/>
The XML data type is represented in mapping schemas as xs:Any elements, as shown in Listing 12-26.
Listing 12-26. XML representation of UDT
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="xmltab" sql:relation="xmltab" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="thexml" sql:field="thexml"
sql:datatype="xml" type="xs:anyType"/>
</xsd:sequence>
</xsd:complexType>
<xsd:attribute name="id" type="xs:int"/>
</xsd:element>
</xsd:schema>
This makes them available in most SqlXml features that use mapping schemas—namely, updategrams—and SQL bulk load. You cannot use XPath template queries with mapping schemas to query into the XML data type column, however. To query an XML data type column, use the server-side XQuery support. Because XQuery is a superset of XPath, XPath queries will also work. CLR-based UDT are also supported, although there is no special support in mapping schemas for UDTs. They are used by declaring them as xsd:string columns and passing the binary representation. This works using mapping schemas for XPath queries or XML Bulk Load.
The SQLXML .NET Framework API
SQLXML 4.0 comes with a .NET Framework API that uses the .NET Framework System.IO.Stream object as input and output. The SqlXml support consists of a series of classes used to call the SQLXML OLE DB–based functionality. These classes are not a .NET Framework data provider as such, but they will be familiar to programmers who have worked with .NET Framework data providers like SqlClient. Table 12-1 lists the SqlXml managed classes and the major properties and methods on each class.
Table 12-1. SqlXml Managed Classes
A distinction should be made for clarity between this library, Microsoft. Data.SqlXml, and the System.Data.SqlXml library that we describe in A First Look at SQL Server 2005 for Developers, by Bob Beauchemin, Niels Berglund, and Dan Sullivan (Addison-Wesley, 2004). System.Data.SqlXml will not expose any public functionality in the .NET Framework 2.0 release. The Microsoft.Data.SqlXml library is an extension of SQLXML 3.0 functionality and encapsulates the OLE DB provider functionality. One thing to consider is that the XSLT processor used when you specify the XSLPath property of SqlXmlCommand is the processor in MSXML 6.0, not either of the processors in System.Xml.dll. If a specific feature of .NET Framework XSLT is required, you can easily feed the stream from SqlXmlCommand into these processors.
SQLXML Bulk Load Component
SQLXML Bulk Load is a COM component that uses SQLXML mapping schemas to permit loading large XML files in bulk. A new version of this component, SQLXMLBulkLoad4, can be used only with SQL Server 2005 databases because it makes use of the built-in XML data type in that release. It’s been improved to support of decomposition into the XML and CLR UDT data type columns. Note that you can still use SQLOLEDB as the provider in the connection string for SQLXML Bulk Load. The program in Listing 12-27 will work using the XML data type with the mapping schema shown in Listing 12-28.
Listing 12-27. SQLXML bulk loader
// STAThread attribute is required
[STAThread]
static void Main(string[] args)
{
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class b = new
SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
b.ConnectionString =
"Provider=sqloledb;server=svr;database=pubs;integrated
security=SSPI";
b.ErrorLogFile = "error.xml";
b.KeepIdentity = false;
b.Execute ("mapping_schema.xml","data.xml");
}
When using a UDT column, you must use the binary form of the XML; the SQLXMLBulkLoad component does not send a string value to SQL Server, even though SQL Server can do an implicit conversion from string to UDT. The mapping_schema and data files are in Listing 12-28 and Listing 12-29.
Listing 12-28. mapping_schema.xml
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="CTab" sql:relation="CTab" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="id"
type="xsd:int"></xsd:element>
<xsd:element name="CNum"
type="xsd:string"></xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
</ex>
Listing 12-29. data.xml
<ROOT>
<CTab>
<id>1</id>
<CNum>01C02400000000000001C02400000000000000480000</CNum>
</CTab>
</ROOT>
One final enhancement is the use of the XML data type as an overflow column when you specify the SchemaGen option to generate a relational-database schema from the XML mapping schema. Listing 12-30 shows the use SchemaGen and Visual Basic Script.
Listing 12-30. SchemaGen option
Dim objBL
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=localhost;
database=tempdb;integrated security=SSPI"
objBL.ErrorLogFile = "c:error.log"
objBL.CheckConstraints=true
objBL.XMLFragment = True
objBL.SchemaGen = True
objBL.SGDropTables = True
objBL.Execute "SampleSchema.xml", "SampleXMLData.xml"
Set objBL = Nothing
Now there are at least three different ways to get XML inside the SQL Server database if you have SQL Server 2005:
• In-database decomposition using OpenXML
• In-database decomposition using XML nodes
• BULK INSERT using the new BULK OLE DB provider
So what is the best practice for deciding which functionality to use? All the scenarios presented for SQLXML 3.0 in SQL Server 2005 in the paper “XML Options in Microsoft SQL Server 2005,” available on MSDN, are still valid when using SQLXML 4.0. If there is a lot of preprocessing of SQLXML, Bulk Load is preferred. SQLXML also makes it easier to code if you have diverse clients that provide XML in slightly different formats; you need to change the mapping schema, rather than the code. In-database loading and decomposition are preferred if you have enough processing power to accomplish this in the database, have homogeneous XML, or want to import individual XML files into rows in a table with an XML column. OpenXml is the decomposition method that is supported in SQL Server 2000, as well as SQL Server 2005. It has been slightly enhanced in SQL Server 2005 to support the XML data type, but because it creates an in-memory representation of an XML DOM, XML nodes, or client-side SQLXML, bulk copy is a better choice for large volumes of XML. You can even use the new SQL BULK INSERT provider to insert multiple XML documents into multiple rows in a table with an XML data type column, but these documents have to have a row separator in the file. There is also the possibility of using System.Data.Xml in a .NET Framework procedure or function if you need some special functionality that none of the components that come in the box provides.
Web Service and Other Web Support
SQLXML 3.0’s IIS/ISAPI functionality, including Web Services, isn’t included with SQLXML 4.0. If you used this functionality for anything but trivial demos, you probably wrote code that preprocessed or postprocessed the HTTP data stream. You can still use this code with SQL Server 2005, but for forward compatibility with future Web Service specifications, you’ll probably want to switch to using ASP.NET and the .NET Framework SqlXml managed classes. SQLXML 3.0 and 4.0 use version-dependent Class IDs, so you can even install them side by side as you’re doing your conversion. In addition, in Intranet scenarios where you just want to communicate with the database using SOAP, the HTTP Endpoint support in SQL Server 2005 will serve your needs admirably. You can even pass the stream from IIS into SQL Server 2005’s endpoints and integrate with future enhancements to the Service Oriented Architecture at the Web server level. The useful feature of HTTP endpoints is that the metadata for the SOAP operations is stored in the database using DDL rather than being stored in the Windows Registry, as in SQLXML 3.0. Listing 12-31 shows a simple HTTP endpoint definition for exposing a stored procedure as a Web Service.
Listing 12-31. SQLXML 3.0 Web Service
CREATE ENDPOINT pubs_endpoint
STATE = STARTED
AS HTTP
(
SITE = '*',
PATH = '/pubs',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR)
)
FOR SOAP
(
WEBMETHOD 'urn:www-develop-com:invoices'.'byroyalty'
(
name='pubs.dbo.byroyalty',
SCHEMA = STANDARD
),
BATCHES = ENABLED,
WSDL = DEFAULT,
DATABASE = 'pubs'
)
GO
The HTTP endpoints do not support SQLXML templates directly, as SQLXML 3.0 did, but you can write stored procedures exposed as Web Services in T-SQL or .NET Framework that do XML processing using XQuery or System.Data.Xml. You can even write custom WSDL for your Web Service to expand support to diverse groups of users. For more information on HTTP Endpoints, reference the papers “Overview of Native XML Web Services for Microsoft SQL Server 2005” and “Usage Scenarios for SQL Server 2005 Native Web Services,” by Srik Raghavan and Brad Sarsfield, on the MSDN Web site.
The client-side processing component and mapping-schema architecture in SQLXML 3.0 have been enhanced for SQL Server 2005, and additional storage and decomposition facilities have been added both outside and inside the database. SQLXML 4.0 is not a complete rewrite of SQLXML 3.0, but an evolutionary enhancement. Programmers can continue to use the same programming model as they move toward ASP.NET and Web Services.
Where Are We?
SQL Server 2005 provides an alternative way to make a connection via a Web Service. The Web Service can provide the functionality that applications typically expect from a TDS connection, but with only the tools provided by most Web Service development environments. Web Service connections support authentication of the user in the same way that TDS connections do.
SQL Sever 2005 can generate a WSDL file that is compatible with virtually all development environments for Web Services. Visual Studio 2005 was designed with SQL Server 2005 in mind and can use the SQL Server 2005–specific information in the WSDL file.
SQL Server 2005 can host stored procedures, user-defined functions, and arbitrary SQL batches in a Web Service.
Web Services in SQL Server 2005 support functionality tightly bound to the Windows environment and much more loosely coupled functionally via industry-standard XML specifications.