Building T-SQL Custom WSDL generator

Building T-SQL Custom WSDL generator

  • Comments 1

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

Leave a Comment
  • Please add 4 and 5 and type the answer here:
  • Post
  • Commonly for Web Services, the exposed interfaces (webmethods) must conform to some pre-defined contract.

Page 1 of 1 (1 items)