Building T-SQL Custom WSDL generator

[SQL-Server-Team]

First published on MSDN on Nov 07, 2006

We've gotten feedback from customers who have asked if it is possible to write a custom WSDL generator in T-SQL.  For those customers who do not wish to enable SQL CLR support, the following sample T-SQL SP can be used as a starting point for generating your own custom WSDL.  Please be advised that this sample is just as that, a sample.  It is NOT production level code and is provided as a technical demonstration that it is possible.

NOTE: The server response format for a SP can not be changed, unless you specify FORMAT = NONE on the ENDPOINT WEBMETHOD keyword syntax.  At which point, you are responsible to control the exact response.

This post will discuss just the WSDL portion.  A seperate posting later on will discuss how to customize SP response formats.  Please note that this post contains the entire T-SQL SP code and as such is a long posting.  The sample code is below:

CREATE PROCEDURE SpHttpGenerateWsdl
@EndpointID int,
@IsSSL bit,
@Host nvarchar(128),
@QueryString nvarchar(128),
@UserAgent nvarchar(128)
as
begin
set nocount on
declare @http int
set @http = 1
declare @soap int
set @soap = 1
declare @started int
set @started = 0
declare @outputWSDL nvarchar(max)

-- define the set of preset strings needed in the WSDL document
set @outputWSDL = N'<wsdl:definitions xmlns:wsdl=" http://schemas.xmlsoap.org/wsdl/ " xmlns:soap=" http://schemas.xmlsoap.org/wsdl/soap/"'
declare @wsdlStartTypes nvarchar(50)
set @wsdlStartTypes = N'<wsdl:types>'
declare @wsdlEndTypes nvarchar(20)
set @wsdlEndTypes = N'</wsdl:types>'
declare @xsdStartSchema nvarchar(150)
set @xsdStartSchema = N'<xsd:schema xmlns:xsd=" http://www.w3.org/2001/XMLSchema " attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace='
declare @xsdEndSchema nvarchar(20)
set @xsdEndSchema = N'</xsd:schema>'
declare @xsdStartElement nvarchar (50)
set @xsdStartElement = N'<xsd:element name='
declare @xsdEndElement nvarchar(20)
set @xsdEndElement = N'</xsd:element>'
declare @xsdStartComplexType nvarchar(50)
set @xsdStartComplexType = N'<xsd:complexType><xsd:sequence>'
declare @xsdEndComplexType nvarchar(50)
set @xsdEndComplexType = N'</xsd:sequence></xsd:complexType>'
declare @wsdlStartMessage nvarchar(100)
set @wsdlStartMessage = N'<wsdl:message name='
declare @wsdlEndMessage nvarchar(20)
set @wsdlEndMessage = N'</wsdl:message>'
declare @wsdlStartPart nvarchar(50)
set @wsdlStartPart = N'<wsdl:part name="parameters" element='
declare @wsdlEndPart nvarchar(20)
set @wsdlEndPart = N'</wsdl:part>'
declare @wsdlStartPortType nvarchar(50)
set @wsdlStartPortType = N'<wsdl:portType name='
declare @wsdlEndPortType nvarchar(20)
set @wsdlEndPortType = N'</wsdl:portType>'
declare @wsdlStartOperation nvarchar(50)
set @wsdlStartOperation = N'<wsdl:operation name='
declare @wsdlEndOperation nvarchar(20)
set @wsdlEndOperation = N'</wsdl:operation>'
declare @wsdlStartInput nvarchar(50)
set @wsdlStartInput = N'<wsdl:input name='
declare @wsdlEndInput nvarchar(20)
set @wsdlEndInput = N'</wsdl:input>'
declare @wsdlStartOutput nvarchar(50)
set @wsdlStartOutput = N'<wsdl:output name='
declare @wsdlEndOutput nvarchar(20)
set @wsdlEndOutput = N'</wsdl:output>'
declare @wsdlStartBinding nvarchar(50)
set @wsdlStartBinding = N'<wsdl:binding name='
declare @wsdlEndBinding nvarchar(20)
set @wsdlEndBinding = N'</wsdl:binding>'
declare @soapBinding nvarchar(100)
set @soapBinding = N'<soap:binding transport=" http://schemas.xmlsoap.org/soap/http " style="document"/>'
declare @soapStartOperation nvarchar(50)
set @soapStartOperation = N'<soap:operation soapAction='
declare @soapEndOperation nvarchar(20)
set @soapEndOperation = N' style="document" />'
declare @soapBody nvarchar(50)
set @soapBody = N'<soap:body use="literal" />'
declare @wsdlStartService nvarchar(50)
set @wsdlStartService = N'<wsdl:service name='
declare @wsdlEndService nvarchar(20)
set @wsdlEndService = N'</wsdl:service>'
declare @wsdlStartPort nvarchar(50)
set @wsdlStartPort = N'<wsdl:port name='
declare @wsdlEndPort nvarchar(20)
set @wsdlEndPort = N'</wsdl:port>'
declare @soapStartAddress nvarchar(50)
set @soapStartAddress = N'<soap:address location='
declare @soapEndAddress nvarchar(20)
set @soapEndAddress = N'</soap:address>'
declare @wsdlEndDefinitions nvarchar(20)
set @wsdlEndDefinitions = N'</wsdl:definitions>'

-- some local variables
declare @endpointWsdl nvarchar(100)
declare @endpointProtocol int
declare @endpointType int
declare @endpointState int
declare @endpointBatches bit
declare @endpointMethodCount int

-- make sure WSDL is enabled on the endpoint
select @endpointWsdl = wsdl_generator_procedure from sys.soap_endpoints where endpoint_id = @EndpointID
if (NOT (LEN(@endpointWsdl) > 0))
begin
raiserror ('WSDL generation is disabled for this endpoint.', 16, 1)
end

-- make sure the query string is requesting for WSDL
if (N'WSDL' <> UPPER(@QueryString))
begin
raiserror ('Unsupported Action, please double check value of query string.', 16, 1)
end

-- make sure the endpoint actually exists
if ((select endpoint_id from sys.endpoints where endpoint_id = @EndpointID) is NULL)
begin
raiserror ('Specified Endpoint is invalid.', 16, 1)
end

-- make sure the endpoint is a SOAP endpoint and is started
select @endpointProtocol=protocol,
@endpointType=type,
@endpointState=state
from sys.endpoints where endpoint_id = @EndpointID

if ((@endpointProtocol <> @http) OR (@endpointType <> @soap) OR (@endpointState <> @started))
begin
RAISERROR('Specified Endpoint is not a SOAP endpoint or is not started', 16, 1)
end

-- query to see if SqlBatch is enabled on the endpoint
select @endpointBatches = is_sql_language_enabled from sys.soap_endpoints where endpoint_id = @EndpointID

-- check the number of web methods specified on the endpoint
select @endpointMethodCount = count(*) from sys.endpoint_webmethods where endpoint_id = @EndpointID

-- if SqlBatch is enabled or if there is at least one web method, then generate WSDL
if ((@endpointBatches = 1) OR (@endpointMethodCount > 0))
begin
-- Note: this sample does not actually general the definition for the SqlBatch method
-- create a temp table to store the list of webmethods on the endpoint
create table #tempWSDLMethod (db nvarchar(20) NOT NULL,
oOwner nvarchar(20) NOT NULL,
oName nvarchar(50) NOT NULL)

insert #tempWSDLMethod (db, oOwner, oName)
select LEFT(object_name, CHARINDEX(N'.', object_name)-1),
SUBSTRING(object_name, CHARINDEX(N'.', object_name)+1, CHARINDEX(N'.', object_name, CHARINDEX(N'.', object_name)+1)-CHARINDEX(N'.', object_name)-1),
RIGHT(object_name, LEN(object_name)-CHARINDEX(N'.', object_name, CHARINDEX(N'.', object_name)+1))
from sys.endpoint_webmethods where endpoint_id = @EndpointID

-- create a temp table to keep track of all the information needed to generate WSDL
create table #tempWSDLTable (id int identity primary key,
webMethodNamespace nvarchar(max) NOT NULL,
webMethodName nvarchar(max) NOT NULL,
paramName nvarchar(100) NOT NULL,
namespaceSuffix int DEFAULT 1)

-- insert appropriate info to temp table
declare webMethodDb_Cursor CURSOR FOR
SELECT distinct db from #tempWSDLMethod

declare @tDbName nvarchar(50)
open webMethodDb_Cursor
FETCH NEXT FROM webMethodDb_Cursor INTO @tDbName
WHILE @@FETCH_STATUS = 0
BEGIN
declare @tQuery nvarchar(max)
set @tQuery = N'use ' + @tDbName + '; insert #tempWSDLTable (webMethodNamespace, webMethodName, paramName)
select a.namespace, a.method_alias, RIGHT(b.name, (LEN(b.name)-1))
from sys.endpoint_webmethods as a, sys.parameters as b
where a.endpoint_id = ' + CAST(@EndpointID as nvarchar(10))
+ N' and b.object_id = object_id(a.object_name) and (LEN(b.name) > 0)'
exec (@tQuery)
FETCH NEXT FROM webMethodDb_Cursor INTO @tDbName
END

CLOSE webMethodDb_Cursor
DEALLOCATE webMethodDb_Cursor

-- generate the WSDL document
select @outputWSDL = @outputWSDL + N' xmlns:tns="' + default_namespace +
'" targetNamespace="' + default_namespace + '"'
from sys.soap_endpoints where endpoint_id = @EndpointID

declare webMethodNS_Cursor SCROLL CURSOR FOR
SELECT distinct webMethodNamespace from #tempWSDLTable

declare @tCount int
declare @tMethodNS varchar(50)
set @tCount = 1
open webMethodNS_Cursor
FETCH NEXT FROM webMethodNS_Cursor INTO @tMethodNS
WHILE @@FETCH_STATUS = 0
BEGIN
select @outputWSDL = @outputWSDL + N' xmlns:s' + CAST(@tCount as nvarchar(3)) + N'="' + @tMethodNS + N'"'
update #tempWSDLTable set namespaceSuffix = @tCount where webMethodNamespace = @tMethodNS
set @tCount = @tCount + 1
FETCH NEXT FROM webMethodNS_Cursor INTO @tMethodNS
END

select @outputWSDL = @outputWSDL + N'>'

-- start the <wsdl:types> node
select @outputWSDL = @outputWSDL + @wsdlStartTypes
-- add any xsd:schema as necessary here
-- one possibility is to store these XML schemas in a table and query the appropriate ones here
-- and add them to the WSDL

-- loop through the set of webmethod namespaces to add the appropriate xsd schema definitions
FETCH FIRST FROM webMethodNS_Cursor INTO @tMethodNS
WHILE @@FETCH_STATUS = 0
BEGIN
select @outputWSDL = @outputWSDL + @xsdStartSchema + N'"' + @tMethodNS + N'">'

declare webMethodInfo_Cursor CURSOR FOR
SELECT DISTINCT webMethodName, paramName
from #tempWSDLTable
where webMethodNamespace = @tMethodNS
order by webMethodName ASC

declare @tMethodName nvarchar(50)
declare @tMethodNameBak nvarchar(50)
declare @tMethodParamName nvarchar(20)
declare @bFirstTime bit
set @tMethodNameBak = N''
set @bFirstTime = 1
open webMethodInfo_Cursor
FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodParamName
WHILE @@FETCH_STATUS = 0
BEGIN
if ((NOT (@bFirstTime = 1) AND (@tMethodNameBak <> @tMethodName)))
begin
-- close out the method name node
select @outputWSDL = @outputWSDL + @xsdEndComplexType
select @outputWSDL = @outputWSDL + @xsdEndElement

-- response message structure
select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Response">'
select @outputWSDL = @outputWSDL + @xsdStartComplexType
select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Result">'
select @outputWSDL = @outputWSDL + @xsdEndElement
select @outputWSDL = @outputWSDL + @xsdEndComplexType
select @outputWSDL = @outputWSDL + @xsdEndElement
end

-- request message structure
if (@tMethodNameBak <> @tMethodName)
begin
-- add the method name node
select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodName + N'">'
select @outputWSDL = @outputWSDL + @xsdStartComplexType
end

-- add the parameters
-- Make sure the appropriate parameter type is specified here
-- This sample leaves it as xsd:anyType which is normally handled as an Object
select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodParamName + N'">'
select @outputWSDL = @outputWSDL + @xsdEndElement

set @bFirstTime = 0
set @tMethodNameBak = @tMethodName
FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodParamName
END

-- close out the method name node
select @outputWSDL = @outputWSDL + @xsdEndComplexType
select @outputWSDL = @outputWSDL + @xsdEndElement

-- response message structure
select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Response">'
select @outputWSDL = @outputWSDL + @xsdStartComplexType
select @outputWSDL = @outputWSDL + @xsdStartElement + N'"' + @tMethodNameBak + N'Result">'
select @outputWSDL = @outputWSDL + @xsdEndElement
select @outputWSDL = @outputWSDL + @xsdEndComplexType
select @outputWSDL = @outputWSDL + @xsdEndElement

CLOSE webMethodInfo_Cursor
DEALLOCATE webMethodInfo_Cursor

select @outputWSDL = @outputWSDL + @xsdEndSchema

FETCH NEXT FROM webMethodNS_Cursor INTO @tMethodNS
END

CLOSE webMethodNS_Cursor
DEALLOCATE webMethodNS_Cursor

-- close the <wsdl:types> node
select @outputWSDL = @outputWSDL + @wsdlEndTypes

-- need to loop through each webmethod on the endpoint
declare webMethodInfo_Cursor SCROLL CURSOR FOR
SELECT DISTINCT webMethodName, webMethodNamespace, namespaceSuffix
from #tempWSDLTable
ORDER BY webMethodNamespace ASC, webMethodName ASC

declare @tIdSuffix int

open webMethodInfo_Cursor
FETCH FIRST FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
WHILE @@FETCH_STATUS = 0
BEGIN
-- the <wsdl:message> node
select @outputWSDL = @outputWSDL + @wsdlStartMessage + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapIn">'
select @outputWSDL = @outputWSDL + @wsdlStartPart + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N':' + @tMethodName + N'" />'
select @outputWSDL = @outputWSDL + @wsdlEndMessage
select @outputWSDL = @outputWSDL + @wsdlStartMessage + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapOut">'
select @outputWSDL = @outputWSDL + @wsdlStartPart + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N':' + @tMethodName + N'Response" />'
select @outputWSDL = @outputWSDL + @wsdlEndMessage
FETCH Next FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
END

-- the <wsdl:portType> node
select @outputWSDL = @outputWSDL + @wsdlStartPortType + N'"' + name + N'Soap">'
from sys.http_endpoints where endpoint_id = @EndpointId

FETCH FIRST FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
WHILE @@FETCH_STATUS = 0
BEGIN
-- the <wsdl:Operation> node
select @outputWSDL = @outputWSDL + @wsdlStartOperation + N'"' + @tMethodName + N'">'
select @outputWSDL = @outputWSDL + @wsdlStartInput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName +
N'SoapIn" message="tns:s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapIn" />'
select @outputWSDL = @outputWSDL + @wsdlStartOutput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName +
N'SoapOut" message="tns:s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapOut" />'
select @outputWSDL = @outputWSDL + @wsdlEndOperation
FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
END

select @outputWSDL = @outputWSDL + @wsdlEndPortType

-- the <wsdl:binding> node
select @outputWSDL = @outputWSDL + @wsdlStartBinding + N'"' + name + N'Soap" type="tns:' + name + N'Soap">'
from sys.http_endpoints where endpoint_id = @EndpointID

select @outputWSDL = @outputWSDL + @soapBinding

FETCH FIRST FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
WHILE @@FETCH_STATUS = 0
BEGIN
select @outputWSDL = @outputWSDL + @wsdlStartOperation + N'"' + @tMethodName + N'">'
select @outputWSDL = @outputWSDL + @soapStartOperation + N'"' + @tMethodNS + @tMethodName + N'"' + @soapEndOperation
select @outputWSDL = @outputWSDL + @wsdlStartInput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapIn">'
select @outputWSDL = @outputWSDL + @soapBody + @wsdlEndInput
select @outputWSDL = @outputWSDL + @wsdlStartOutput + N'"s' + CAST(@tIdSuffix as nvarchar(3)) + N'Msg' + @tMethodName + N'SoapOut">'
select @outputWSDL = @outputWSDL + @soapBody + @wsdlEndOutput + @wsdlEndOperation
FETCH NEXT FROM webMethodInfo_Cursor INTO @tMethodName, @tMethodNS, @tIdSuffix
END

CLOSE webMethodInfo_Cursor
DEALLOCATE webMethodInfo_Cursor

select @outputWSDL = @outputWSDL + @wsdlEndBinding

select @outputWSDL = @outputWSDL + @wsdlStartService + N'"' + name + N'">'
from sys.http_endpoints where endpoint_id = @EndpointID

select @outputWSDL = @outputWSDL + @wsdlStartPort + N'"' + name + N'" binding="tns:' + name + N'Soap">'
from sys.http_endpoints where endpoint_id = @EndpointID

if (@IsSSL = 1)
begin
select @outputWSDL = @outputWSDL + @soapStartAddress + N'" https://' + @Host + N'/' + url_path + N'" />'
from sys.http_endpoints where endpoint_id = @EndpointID
end
else
begin
select @outputWSDL = @outputWSDL + @soapStartAddress + N'" http://' + @Host + N'/' + url_path + N'" />'
from sys.http_endpoints where endpoint_id = @EndpointID
end

select @outputWSDL = @outputWSDL + @wsdlEndPort + @wsdlEndService + @wsdlEndDefinitions
end

-- The WSDL document must be returned to the client using this GUID as the column name.
select @outputWSDL as N'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
end
go

Jimmy Wu, SQL Server Protocols
Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights