XML Service for native Microsoft SQL Server 2005
Microsoft provides a standard mechanism for accessing the database engine using SOAP via HTTP in SQL server 2005. Web services are Application Programming Interfaces, or web APIs, that will be accessed via the Hypertext Transfer Protocol and can be executed on a remote system. In order to run the following example, you have to install IIS.
HTTP/SOAP Endpoint
A Service listens for requests natively within the server. All of the endpoint supports a protocol, which can be HTTP or TCP. It also supports database mirroring, T-SQL or SOAP.
First you have to decide which object you are going to use from SQL Server. You can use stored procedures and user defined functions as endpoints, but you cannot use table and views.
Here is an explanation of endpoints with a simple example.
CREATE PROC uspGetPhoneInfo
AS
BEGIN
SET NOCOUNT ON
SELECT Name,Number from PhoneBook
ORDER BY NAME
END
GO
The above stored procedure will return the list of names and phone numbers from the PhoneBook Table.
The next step is to create the endpoint that allows access to the stored procedure as a web service.
CREATE ENDPOINT GetPhoneBook
STATE = STARTED
AS HTTP
(
PATH = '/PhoneBook',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
CLEAR_PORT = 8088,
SITE = 'localhost'
)
FOR SOAP
(
WEBMETHOD 'GetPhoneBook'
(NAME='TestDB.dbo.uspGetPhoneInfo'),
BATCHES = DISABLED,
WSDL = DEFAULT,
DATABASE = 'TestDB',
NAMESPACE = 'http://localhost/PhoneBook'
)
GO
Executing the above code will create the endpoint. You can access the endpoint in the collection section.
Server Objects→EndPoint→SOAP
In the above example there are some clauses used for different purposes. They are:
- STATE – Specify the initial state of the endpoint. It can be started or stopped.
- HTTP - Specify the transport protocol to use.
- PATH - Specify the URL on the server and which clients are going to use this web service.
- AUTHENTICATION - Specify how the clients will authenticate to the SQL server.
- PORTS - specify the service that listens on the CLEAR or SSL ports.
- SITE – Specify the hostname of the computer which is responding to the request.
- FOR SOAP - Specify which endpoint will respond to SOAP messages.
- Web Method - Define Web method name to the name of a stored procedure.
- BATCHES – Specify if the endpoint will not process SQL statements.
- The WSDL - Provide WSDL Support.
- DATABASE - Specify which database contains the data.
- NAMESPACE - Specify the XML namespace for the message.
You can test the above web service using Internet Explorer.
Type the following URL
http://localhost:8088/PhoneBook?wsdl
“8088” is the port number.
“PhoneBook” is the name space.
Specify “?wsdl” in the end.
The list of xml code will be displayed as shown in the below image.
WSDL
WSDL Is a document output in XML format that describes the Web service. It specifies the location of the service and the operations. WSDL provides the necessary information for a client to access with a Web service. You can use Visual Studio .Net and JBuilder to communicate with the Web service. If the endpoint has “WSDL” enabled, it will produce WSDL when it receives a request. Hence the http request is:
http://localhost:8088/PhoneBook?wsdl
The server generates the WSDL dynamically. The generated WSDL provides a rich description of the parameters from the stored procedure. In the same manner, SQL Server has the ability to generate different type of WSDL, which can be called Complex WSDL.
Administration and Management
It is very simple to create the endpoint and submit the SOAP. Administration is very easy here because we only have to administer one component, which is SQL Server. We need not administer the IIS component. Also, it eliminates the need to open different ports because we can reuse the port used by http.
Final Thoughts
With the help of native SOAP access, we can provide a good protocol based on documented standards, such as SOAP/HTTP, to access SQL Server.