Native XML Web Services in SQL Server 2005

These days, you should hardly be surprised when you run into a business requirement to make data available over the Web. Fortunately, database products are making it easier and easier to hook up Internet protocols directly to your data. Microsoft SQL Server is a case in point. While SQL Server 2000 did allow Web data access, the process had a certain Rube Goldberg feel to it, requiring the use of the SQLXML library and a full installation of Internet Information Services. With SQL Server 2005, Microsoft has revisited its database Web access story, and the story is much improved. Now, you can have direct access to your SQL Server data from any HTTP SOAP client without any extra middleware at all - not even IIS. In this article, I'll show you how it works.

SQL Server Data as a Web Service

Web Services may not be getting quite the flurry of publicity that they were seeing for a few years, but they've developed into an essential part of connectivity for many applications. Microsoft continues to bet heavily on Web Services as a backbone for service-oriented architectures, so it makes sense that they've chosen to expose SQL Server 2005 data via a Web Service. Or, to be more precise, you can create as many Native XML Web Services as you like in SQL Server 2005.

To do so, you use the new CREATE ENDPOINT statement to create HTTP endpoints. Each HTTP endpoint ties the results of a SQL Server stored procedure directly to a Web Service, providing support through the Web Services Description Language (WSDL) and Simple Object Access Protocol (SOAP) protocols for retrieving the data supplied by the stored procedure. SQL Server interfaces directly with the Windows HTTP listener process (http.sys) so that SOAP requests are routed directly to SQL Server, with no intervening middleware necessary. Similarly, SOAP responses are sent directly back to the requesting client.

That's the theory. Let's look at the practice.

Creating an HTTP Endpoint

To get started, I'll need a stored procedure that returns some data. As an example, I'll use a simple stored procedure that returns the contents of two fields from the Sales.Store table in the AdventureWorks sample database that ships with SQL Server 2005:

CREATE PROC dbo.SalesStoreProc
AS
    SELECT 
        CustomerID,
        Name
    FROM
    Sales.Store

The next step is to create the HTTP endpoint. Running this SQL statement is what makes the data from the stored procedure available to SOAP clients:

CREATE ENDPOINT GetStores
    STATE = STARTED
AS HTTP
(
    PATH = '/Store',
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR),
    SITE = 'localhost'
)
FOR SOAP
(
    WEBMETHOD 'StoreList'
        (NAME='AdventureWorks.dbo.SalesStoreProc'),
    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = 'AdventureWorks',
    NAMESPACE = 'http://AdventureWorks/Store'
)
GO

As you can see, there are quite a few clauses to the CREATE ENDPOINT statement. Here's a quick rundown to get you oriented:

• The STATE clause specifies the initial state of the endpoint. It can be started, stopped (listening but returning errors to clients) or disabled (not even listening for requests)
• The AS HTTP clause specifies the transport protocol to use. You can also specify AS TCP here.
• The PATH clause specifies the URL on the server that clients will use to reach this Web service.
• The AUTHENTICATION clause specifies how clients will authenticate themselves to the SQL Server: BASIC, DIGEST, NTLM, KERBEROS, or INTEGRATED.
• The PORTS clause specifies whether the service will listen on the CLEAR or SSL ports, or both (other clauses, not shown here, let you specify non-standard port numbers)
• The SITE clause lets you specify a hostname for the computer that will respond to requests.
• The FOR SOAP clause states that this endpoint will respond to SOAP messages. Other endpoints handle messages for Service Broker or database mirroring.
• The WEBMETHOD clause defines a Web method, mapping a method name to the name of a stored procedure
• The BATCHES clause specifies that this endpoint won't process arbitrary SQL statements.
• The WSDL clause specifies that it will provide WSDL support.
• The DATABASE clause specifies the database that contains the data.
• The NAMESPACE clause specifies the XML namespace for the messages.

Testing the Web Service

There are many tools that will let you test a Web Service that uses SOAP for communication - that's one of the advantages to using a standard protocol. I've chosen to use Altova's XMLSPY for testing, because it lets me see the exact SOAP request and response messages.

To generate the SOAP request, I selected Create New Soap Request from the SOAP menu. This opens the WSDL File Location dialog box, shown in Figure 1.

The URL for the WSDL file (http://localhost/Store?wsdl) is determined by the CREATE ENDPOINT statement. I'm working on the same computer where SQL Server 2005 is running, so the Web server itself is localhost; the PATH clause dictates the Store portion of the URL. The remainder of the URL is where SQL Server listens, by convention, for WSDL requests associated with this particular Web Service.

XMLSPY retries the generated WSDL file and parses it for a list of operations. In this case, there's only the single StoreList operation that was defined by the WEBMETHOD clause in the CREATE ENDPOINT statement. When I select that operation, XMLSPY uses the information in the WSDL file to generate a SOAP request for the information supplied by that operation:

<SOAP-ENV:Envelope 

 xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
 xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
 xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SOAP-ENV:Body>
    <m:StoreList xmlns:m="http://AdventureWorks/Store"/>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>

Then it's simply a matter of selecting Send Request to Server from the SOAP menu in XMLSPY to send this SOAP Request to the HTTP endpoint. SQL Server receives the request, processes it, and sends back a SOAP response containing the requested data. The whole response runs to about 2800 lines (the stored procedure returns over 700 rows of data); here's an excerpt:

<?xml version="1.0" encoding="utf-8"?>
<SOAP-ENV:Envelope xml:space="preserve" 
 xmlns:xsd="http://www.w3.org/2001/XMLSchema"
 [additional namespace declarations omitted]
 xmlns:method="http://AdventureWorks/Store">
  <SOAP-ENV:Body>
    <method:StoreListResponse> 
      <method:StoreListResult xmlns="">
        <sqlresultstream:SqlRowSet 
         xsi:type="sqlsoaptypes:SqlRowSet">
          <xsd:schema 
           xmlns:xsd="http://www.w3.org/2001/XMLSchema"
           targetNamespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes"> 
           [schema details omitted]
         </xsd:schema>
         <diffgr:diffgram xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
           <SqlRowSet1 xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
             <row>
               <CustomerID>1</CustomerID>
               <Name>A Bike Store</Name>
             </row>
             <row>
               <CustomerID>2</CustomerID>
               <Name>Progressive Sports</Name>
             </row>
             <row>
               <CustomerID>3</CustomerID>
               <Name>Advanced Bike Components</Name>
             </row>
             [remaining data omitted]
           </SqlRowSet1>
         </diffgr:diffgram>
       </sqlresultstream:SqlRowSet>
       <sqlresultstream:SqlRowCount xsi:type="sqlrowcount:SqlRowCount">
         <sqlrowcount:Count>701</sqlrowcount:Count>
       </sqlresultstream:SqlRowCount>
       <sqlresultstream:SqlResultCode 
        xsi:type="sqlsoaptypes:SqlResultCode">0</sqlresultstream:SqlResultCode>
     </method:StoreListResult>
   </method:StoreListResponse>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
 

And really, that's all there is to it: run one T-SQL statement, and SQL Server will accept, and respond to, standard HTTP SOAP 1.2 requests. This gives you a supremely easy way to hook SQL Server 2005 data into any sort of service-oriented architecture that you can dream up.

Some Final Thoughts

When you find a new tool, the temptation is always to start using it in all of your development. But before you rush to hook up native XML Web Service for all your SQL Server data needs, you should consider whether it's really the appropriate solution. In the case of this particular technique, there's a pretty clear tradeoff to keep in mind. On the one hand, delivering SQL Server data wrapped up as native SOAP messages means that it's incredibly accessible: you can extend the reach of your data in many cross-platform, cross-language scenarios where it was formerly unavailable. XML and SOAP parsing libraries are ubiquitous, so data delivered in this format can be consumed from just about any software you can imagine. Remember, too, that the delivery mechanism is pure HTTP, which means that you can chip this data anywhere the Internet reaches.

But on the downside, SOAP is necessarily a "fat" format, being text-based (as opposed to the native binary SQL Server Tabular Data Stream, TDS, format). You can expect it to take 20-30% longer to ship data from point A to point B using this technique than if you were building an application that used traditional SQL Server client libraries. If your application uses Windows clients and LAN connectivity, there's little or no point to incurring that level of overhead just to be buzzword-compliant. Keep the Web Service access for times when you need to integrate with service-oriented architectures, and you'll find this to be a useful and powerful tool.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest books, Coder to Developer and Developer to Designer, both from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.