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.