Building T-SQL Custom WSDL generator

Article
2006-11-07
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="https://schemas.xmlsoap.org/wsdl/" xmlns:soap="https://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="https://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="https://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'"https://' + @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
Comments

Anonymous
February 25, 2007
Commonly for Web Services, the exposed interfaces (webmethods) must conform to some pre-defined contract.
Share via

Building T-SQL Custom WSDL generator

Comments

Additional resources
