Home
%3CLINGO-SUB%20id%3D%22lingo-sub-383178%22%20slang%3D%22en-US%22%3EBuilding%20T-SQL%20Custom%20WSDL%20generator%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-383178%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Nov%2007%2C%202006%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EWe've%20gotten%20feedback%20from%20customers%20who%20have%20asked%20if%20it%20is%20possible%20to%20write%20a%20custom%20WSDL%20generator%20in%20T-SQL.%26nbsp%3B%20For%20those%20customers%20who%20do%20not%20wish%20to%20enable%20SQL%20CLR%20support%2C%20the%20following%20sample%20T-SQL%20SP%20can%20be%20used%20as%20a%20starting%20point%20for%20generating%20your%20own%20custom%20WSDL.%26nbsp%3B%20Please%20be%20advised%20that%20this%20sample%20is%20just%20as%20that%2C%20a%20sample.%26nbsp%3B%20It%20is%20%3CSTRONG%3E%20NOT%20%3C%2FSTRONG%3E%20production%20level%20code%20and%20is%20provided%20as%20a%20technical%20demonstration%20that%20it%20is%20possible.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ENOTE%3A%20The%20server%20response%20format%20for%20a%20SP%20can%20not%20be%20changed%2C%20unless%20you%20specify%20FORMAT%20%3D%20NONE%20on%20the%20ENDPOINT%20WEBMETHOD%20keyword%20syntax.%26nbsp%3B%20At%20which%20point%2C%20you%20are%20responsible%20to%20control%20the%20exact%20response.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EThis%20post%20will%20discuss%20just%20the%20WSDL%20portion.%26nbsp%3B%20A%20seperate%20posting%20later%20on%26nbsp%3Bwill%20discuss%20how%20to%20customize%20SP%20response%20formats.%26nbsp%3B%20Please%20note%20that%20this%20post%20contains%20the%20entire%20T-SQL%20SP%20code%20and%20as%20such%20is%20a%20long%20posting.%26nbsp%3B%20The%20sample%20code%20is%20below%3A%3C%2FP%3ECREATE%20PROCEDURE%20SpHttpGenerateWsdl%20%3CBR%20%2F%3E%20%40EndpointID%20int%2C%20%3CBR%20%2F%3E%20%40IsSSL%20bit%2C%20%3CBR%20%2F%3E%20%40Host%20nvarchar(128)%2C%20%3CBR%20%2F%3E%20%40QueryString%20nvarchar(128)%2C%20%3CBR%20%2F%3E%20%40UserAgent%20nvarchar(128)%20%3CBR%20%2F%3E%20as%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20set%20nocount%20on%20%3CBR%20%2F%3E%20declare%20%40http%20int%20%3CBR%20%2F%3E%20set%20%40http%20%3D%201%20%3CBR%20%2F%3E%20declare%20%40soap%20int%20%3CBR%20%2F%3E%20set%20%40soap%20%3D%201%20%3CBR%20%2F%3E%20declare%20%40started%20int%20%3CBR%20%2F%3E%20set%20%40started%20%3D%200%20%3CBR%20%2F%3E%20declare%20%40outputWSDL%20nvarchar(max)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20define%20the%20set%20of%20preset%20strings%20needed%20in%20the%20WSDL%20document%20%3CBR%20%2F%3E%20set%20%40outputWSDL%20%3D%20N'%3CDEFINITIONS%20wsdl%3D%22%26quot%3B%22%3E%3CA%20href%3D%22http%3A%2F%2Fschemas.xmlsoap.org%2Fwsdl%2F%22%20mce_href%3D%22http%3A%2F%2Fschemas.xmlsoap.org%2Fwsdl%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fschemas.xmlsoap.org%2Fwsdl%2F%20%3C%2FA%3E%20%22%20xmlns%3Asoap%3D%22%20%3CA%20href%3D%22http%3A%2F%2Fschemas.xmlsoap.org%2Fwsdl%2Fsoap%2F%26quot%3B'%22%20mce_href%3D%22http%3A%2F%2Fschemas.xmlsoap.org%2Fwsdl%2Fsoap%2F%26quot%3B'%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fschemas.xmlsoap.org%2Fwsdl%2Fsoap%2F%22'%20%3C%2FA%3E%20%3CBR%20%2F%3E%20declare%20%40wsdlStartTypes%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartTypes%20%3D%20N'%3CTYPES%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlEndTypes%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndTypes%20%3D%20N'%3C%2FTYPES%3E'%20%3CBR%20%2F%3E%20declare%20%40xsdStartSchema%20nvarchar(150)%20%3CBR%20%2F%3E%20set%20%40xsdStartSchema%20%3D%20N'%3CSCHEMA%20xsd%3D%22%26quot%3B%22%3E%3CA%20href%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema%22%20mce_href%3D%22http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fwww.w3.org%2F2001%2FXMLSchema%20%3C%2FA%3E%20%22%20attributeFormDefault%3D%22qualified%22%20elementFormDefault%3D%22qualified%22%20targetNamespace%3D'%20%3CBR%20%2F%3E%20declare%20%40xsdEndSchema%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40xsdEndSchema%20%3D%20N'%3C%2FSCHEMA%3E'%20%3CBR%20%2F%3E%20declare%20%40xsdStartElement%20nvarchar%20(50)%20%3CBR%20%2F%3E%20set%20%40xsdStartElement%20%3D%20N'%3CELEMENT%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40xsdEndElement%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40xsdEndElement%20%3D%20N'%3C%2FELEMENT%3E'%20%3CBR%20%2F%3E%20declare%20%40xsdStartComplexType%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40xsdStartComplexType%20%3D%20N'%3CCOMPLEXTYPE%3E%3CSEQUENCE%3E'%20%3CBR%20%2F%3E%20declare%20%40xsdEndComplexType%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40xsdEndComplexType%20%3D%20N'%3C%2FSEQUENCE%3E%3C%2FCOMPLEXTYPE%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlStartMessage%20nvarchar(100)%20%3CBR%20%2F%3E%20set%20%40wsdlStartMessage%20%3D%20N'%3CMESSAGE%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndMessage%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndMessage%20%3D%20N'%3C%2FMESSAGE%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlStartPart%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartPart%20%3D%20N'%3CPART%20name%3D%22%26quot%3Bparameters%26quot%3B%22%20element%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndPart%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndPart%20%3D%20N'%3C%2FPART%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlStartPortType%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartPortType%20%3D%20N'%3CPORTTYPE%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndPortType%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndPortType%20%3D%20N'%3C%2FPORTTYPE%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlStartOperation%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartOperation%20%3D%20N'%3COPERATION%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndOperation%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndOperation%20%3D%20N'%3C%2FOPERATION%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlStartInput%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartInput%20%3D%20N'%3CINPUT%20name%3D%22'%22%20%2F%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndInput%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndInput%20%3D%20N''%20%3CBR%20%2F%3E%20declare%20%40wsdlStartOutput%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartOutput%20%3D%20N'%3COUTPUT%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndOutput%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndOutput%20%3D%20N'%3C%2FOUTPUT%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlStartBinding%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartBinding%20%3D%20N'%3CBINDING%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndBinding%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndBinding%20%3D%20N'%3C%2FBINDING%3E'%20%3CBR%20%2F%3E%20declare%20%40soapBinding%20nvarchar(100)%20%3CBR%20%2F%3E%20set%20%40soapBinding%20%3D%20N'%3CBINDING%20transport%3D%22%26quot%3B%22%3E%3CA%20href%3D%22http%3A%2F%2Fschemas.xmlsoap.org%2Fsoap%2Fhttp%22%20mce_href%3D%22http%3A%2F%2Fschemas.xmlsoap.org%2Fsoap%2Fhttp%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fschemas.xmlsoap.org%2Fsoap%2Fhttp%20%3C%2FA%3E%20%22%20style%3D%22document%22%2F%26gt%3B'%20%3CBR%20%2F%3E%20declare%20%40soapStartOperation%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40soapStartOperation%20%3D%20N'%3COPERATION%20soapaction%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40soapEndOperation%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40soapEndOperation%20%3D%20N'%20style%3D%22document%22%20%2F%26gt%3B'%20%3CBR%20%2F%3E%20declare%20%40soapBody%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40soapBody%20%3D%20N''%20%3CBR%20%2F%3E%20declare%20%40wsdlStartService%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartService%20%3D%20N'%3CSERVICE%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndService%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndService%20%3D%20N'%3C%2FSERVICE%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlStartPort%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40wsdlStartPort%20%3D%20N'%3CPORT%20name%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40wsdlEndPort%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndPort%20%3D%20N'%3C%2FPORT%3E'%20%3CBR%20%2F%3E%20declare%20%40soapStartAddress%20nvarchar(50)%20%3CBR%20%2F%3E%20set%20%40soapStartAddress%20%3D%20N'%3CADDRESS%20location%3D%22'%22%3E%3CBR%20%2F%3E%20declare%20%40soapEndAddress%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40soapEndAddress%20%3D%20N'%3C%2FADDRESS%3E'%20%3CBR%20%2F%3E%20declare%20%40wsdlEndDefinitions%20nvarchar(20)%20%3CBR%20%2F%3E%20set%20%40wsdlEndDefinitions%20%3D%20N'%3C%2FOPERATION%3E'%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20some%20local%20variables%20%3CBR%20%2F%3E%20declare%20%40endpointWsdl%20nvarchar(100)%20%3CBR%20%2F%3E%20declare%20%40endpointProtocol%20int%20%3CBR%20%2F%3E%20declare%20%40endpointType%20int%20%3CBR%20%2F%3E%20declare%20%40endpointState%20int%20%3CBR%20%2F%3E%20declare%20%40endpointBatches%20bit%20%3CBR%20%2F%3E%20declare%20%40endpointMethodCount%20int%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3CP%3E--%20make%20sure%20WSDL%20is%20enabled%20on%20the%20endpoint%20%3CBR%20%2F%3E%20select%20%40endpointWsdl%20%3D%20wsdl_generator_procedure%20from%20sys.soap_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%20%3CBR%20%2F%3E%20if%20(NOT%20(LEN(%40endpointWsdl)%20%26gt%3B%200))%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20raiserror%20('WSDL%20generation%20is%20disabled%20for%20this%20endpoint.'%2C%2016%2C%201)%20%3CBR%20%2F%3E%20end%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20make%20sure%20the%20query%20string%20is%20requesting%20for%20WSDL%20%3CBR%20%2F%3E%20if%20(N'WSDL'%20%26lt%3B%26gt%3B%20UPPER(%40QueryString))%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20raiserror%20('Unsupported%20Action%2C%20please%20double%20check%20value%20of%20query%20string.'%2C%2016%2C%201)%20%3CBR%20%2F%3E%20end%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20make%20sure%20the%20endpoint%20actually%20exists%20%3CBR%20%2F%3E%20if%20((select%20endpoint_id%20from%20sys.endpoints%20where%20endpoint_id%20%3D%20%40EndpointID)%20is%20NULL)%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20raiserror%20('Specified%20Endpoint%20is%20invalid.'%2C%2016%2C%201)%20%3CBR%20%2F%3E%20end%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20make%20sure%20the%20endpoint%20is%20a%20SOAP%20endpoint%20and%20is%20started%20%3CBR%20%2F%3E%20select%20%40endpointProtocol%3Dprotocol%2C%20%3CBR%20%2F%3E%20%40endpointType%3Dtype%2C%20%3CBR%20%2F%3E%20%40endpointState%3Dstate%20%3CBR%20%2F%3E%20from%20sys.endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Eif%20((%40endpointProtocol%20%26lt%3B%26gt%3B%20%40http)%20OR%20(%40endpointType%20%26lt%3B%26gt%3B%20%40soap)%20OR%20(%40endpointState%20%26lt%3B%26gt%3B%20%40started))%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20RAISERROR('Specified%20Endpoint%20is%20not%20a%20SOAP%20endpoint%20or%20is%20not%20started'%2C%2016%2C%201)%20%3CBR%20%2F%3E%20end%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20query%20to%20see%20if%20SqlBatch%20is%20enabled%20on%20the%20endpoint%20%3CBR%20%2F%3E%20select%20%40endpointBatches%20%3D%20is_sql_language_enabled%20from%20sys.soap_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20check%20the%20number%20of%20web%20methods%20specified%20on%20the%20endpoint%20%3CBR%20%2F%3E%20select%20%40endpointMethodCount%20%3D%20count(*)%20from%20sys.endpoint_webmethods%20where%20endpoint_id%20%3D%20%40EndpointID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20if%20SqlBatch%20is%20enabled%20or%20if%20there%20is%20at%20least%20one%20web%20method%2C%20then%20generate%20WSDL%20%3CBR%20%2F%3E%20if%20((%40endpointBatches%20%3D%201)%20OR%20(%40endpointMethodCount%20%26gt%3B%200))%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20--%20Note%3A%20this%20sample%20does%20not%20actually%20general%20the%20definition%20for%20the%20SqlBatch%20method%20%3CBR%20%2F%3E%20--%20create%20a%20temp%20table%20to%20store%20the%20list%20of%20webmethods%20on%20the%20endpoint%20%3CBR%20%2F%3E%20create%20table%20%23tempWSDLMethod%20(db%20nvarchar(20)%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20oOwner%20nvarchar(20)%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20oName%20nvarchar(50)%20NOT%20NULL)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Einsert%20%23tempWSDLMethod%20(db%2C%20oOwner%2C%20oName)%20%3CBR%20%2F%3E%20select%20LEFT(object_name%2C%20CHARINDEX(N'.'%2C%20object_name)-1)%2C%20%3CBR%20%2F%3E%20SUBSTRING(object_name%2C%20CHARINDEX(N'.'%2C%20object_name)%2B1%2C%20CHARINDEX(N'.'%2C%20object_name%2C%20CHARINDEX(N'.'%2C%20object_name)%2B1)-CHARINDEX(N'.'%2C%20object_name)-1)%2C%20%3CBR%20%2F%3E%20RIGHT(object_name%2C%20LEN(object_name)-CHARINDEX(N'.'%2C%20object_name%2C%20CHARINDEX(N'.'%2C%20object_name)%2B1))%20%3CBR%20%2F%3E%20from%20sys.endpoint_webmethods%20where%20endpoint_id%20%3D%20%40EndpointID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20create%20a%20temp%20table%20to%20keep%20track%20of%20all%20the%20information%20needed%20to%20generate%20WSDL%20%3CBR%20%2F%3E%20create%20table%20%23tempWSDLTable%20(id%20int%20identity%20primary%20key%2C%20%3CBR%20%2F%3E%20webMethodNamespace%20nvarchar(max)%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20webMethodName%20nvarchar(max)%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20paramName%20nvarchar(100)%20NOT%20NULL%2C%20%3CBR%20%2F%3E%20namespaceSuffix%20int%20DEFAULT%201)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20insert%20appropriate%20info%20to%20temp%20table%20%3CBR%20%2F%3E%20declare%20webMethodDb_Cursor%20CURSOR%20FOR%20%3CBR%20%2F%3E%20SELECT%20distinct%20db%20from%20%23tempWSDLMethod%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20%40tDbName%20nvarchar(50)%20%3CBR%20%2F%3E%20open%20webMethodDb_Cursor%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodDb_Cursor%20INTO%20%40tDbName%20%3CBR%20%2F%3E%20WHILE%20%40%40FETCH_STATUS%20%3D%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20declare%20%40tQuery%20nvarchar(max)%20%3CBR%20%2F%3E%20set%20%40tQuery%20%3D%20N'use%20'%20%2B%20%40tDbName%20%2B%20'%3B%20insert%20%23tempWSDLTable%20(webMethodNamespace%2C%20webMethodName%2C%20paramName)%20%3CBR%20%2F%3E%20select%20a.namespace%2C%20a.method_alias%2C%20RIGHT(b.name%2C%20(LEN(b.name)-1))%20%3CBR%20%2F%3E%20from%20sys.endpoint_webmethods%20as%20a%2C%20sys.parameters%20as%20b%20%3CBR%20%2F%3E%20where%20a.endpoint_id%20%3D%20'%20%2B%20CAST(%40EndpointID%20as%20nvarchar(10))%20%3CBR%20%2F%3E%20%2B%20N'%20and%20b.object_id%20%3D%20object_id(a.object_name)%20and%20(LEN(b.name)%20%26gt%3B%200)'%20%3CBR%20%2F%3E%20exec%20(%40tQuery)%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodDb_Cursor%20INTO%20%40tDbName%20%3CBR%20%2F%3E%20END%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLOSE%20webMethodDb_Cursor%20%3CBR%20%2F%3E%20DEALLOCATE%20webMethodDb_Cursor%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20generate%20the%20WSDL%20document%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20N'%20xmlns%3Atns%3D%22'%20%2B%20default_namespace%20%2B%20%3CBR%20%2F%3E%20'%22%20targetNamespace%3D%22'%20%2B%20default_namespace%20%2B%20'%22'%20%3CBR%20%2F%3E%20from%20sys.soap_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20webMethodNS_Cursor%20SCROLL%20CURSOR%20FOR%20%3CBR%20%2F%3E%20SELECT%20distinct%20webMethodNamespace%20from%20%23tempWSDLTable%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20%40tCount%20int%20%3CBR%20%2F%3E%20declare%20%40tMethodNS%20varchar(50)%20%3CBR%20%2F%3E%20set%20%40tCount%20%3D%201%20%3CBR%20%2F%3E%20open%20webMethodNS_Cursor%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodNS_Cursor%20INTO%20%40tMethodNS%20%3CBR%20%2F%3E%20WHILE%20%40%40FETCH_STATUS%20%3D%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20N'%20xmlns%3As'%20%2B%20CAST(%40tCount%20as%20nvarchar(3))%20%2B%20N'%3D%22'%20%2B%20%40tMethodNS%20%2B%20N'%22'%20%3CBR%20%2F%3E%20update%20%23tempWSDLTable%20set%20namespaceSuffix%20%3D%20%40tCount%20where%20webMethodNamespace%20%3D%20%40tMethodNS%20%3CBR%20%2F%3E%20set%20%40tCount%20%3D%20%40tCount%20%2B%201%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodNS_Cursor%20INTO%20%40tMethodNS%20%3CBR%20%2F%3E%20END%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20N'%26gt%3B'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20start%20the%20%3CTYPES%3E%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartTypes%20%3CBR%20%2F%3E%20--%20add%20any%20xsd%3Aschema%20as%20necessary%20here%20%3CBR%20%2F%3E%20--%20one%20possibility%20is%20to%20store%20these%20XML%20schemas%20in%20a%20table%20and%20query%20the%20appropriate%20ones%20here%20%3CBR%20%2F%3E%20--%20and%20add%20them%20to%20the%20WSDL%3C%2FTYPES%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20loop%20through%20the%20set%20of%20webmethod%20namespaces%20to%20add%20the%20appropriate%20xsd%20schema%20definitions%20%3CBR%20%2F%3E%20FETCH%20FIRST%20FROM%20webMethodNS_Cursor%20INTO%20%40tMethodNS%20%3CBR%20%2F%3E%20WHILE%20%40%40FETCH_STATUS%20%3D%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartSchema%20%2B%20N'%22'%20%2B%20%40tMethodNS%20%2B%20N'%22%26gt%3B'%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20webMethodInfo_Cursor%20CURSOR%20FOR%20%3CBR%20%2F%3E%20SELECT%20DISTINCT%20webMethodName%2C%20paramName%20%3CBR%20%2F%3E%20from%20%23tempWSDLTable%20%3CBR%20%2F%3E%20where%20webMethodNamespace%20%3D%20%40tMethodNS%20%3CBR%20%2F%3E%20order%20by%20webMethodName%20ASC%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20%40tMethodName%20nvarchar(50)%20%3CBR%20%2F%3E%20declare%20%40tMethodNameBak%20nvarchar(50)%20%3CBR%20%2F%3E%20declare%20%40tMethodParamName%20nvarchar(20)%20%3CBR%20%2F%3E%20declare%20%40bFirstTime%20bit%20%3CBR%20%2F%3E%20set%20%40tMethodNameBak%20%3D%20N''%20%3CBR%20%2F%3E%20set%20%40bFirstTime%20%3D%201%20%3CBR%20%2F%3E%20open%20webMethodInfo_Cursor%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodParamName%20%3CBR%20%2F%3E%20WHILE%20%40%40FETCH_STATUS%20%3D%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20if%20((NOT%20(%40bFirstTime%20%3D%201)%20AND%20(%40tMethodNameBak%20%26lt%3B%26gt%3B%20%40tMethodName)))%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20--%20close%20out%20the%20method%20name%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndComplexType%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndElement%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20response%20message%20structure%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartElement%20%2B%20N'%22'%20%2B%20%40tMethodNameBak%20%2B%20N'Response%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartComplexType%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartElement%20%2B%20N'%22'%20%2B%20%40tMethodNameBak%20%2B%20N'Result%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndElement%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndComplexType%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndElement%20%3CBR%20%2F%3E%20end%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20request%20message%20structure%20%3CBR%20%2F%3E%20if%20(%40tMethodNameBak%20%26lt%3B%26gt%3B%20%40tMethodName)%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20--%20add%20the%20method%20name%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartElement%20%2B%20N'%22'%20%2B%20%40tMethodName%20%2B%20N'%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartComplexType%20%3CBR%20%2F%3E%20end%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20add%20the%20parameters%20%3CBR%20%2F%3E%20--%20Make%20sure%20the%20appropriate%20parameter%20type%20is%20specified%20here%20%3CBR%20%2F%3E%20--%20This%20sample%20leaves%20it%20as%20xsd%3AanyType%20which%20is%20normally%20handled%20as%20an%20Object%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartElement%20%2B%20N'%22'%20%2B%20%40tMethodParamName%20%2B%20N'%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndElement%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eset%20%40bFirstTime%20%3D%200%20%3CBR%20%2F%3E%20set%20%40tMethodNameBak%20%3D%20%40tMethodName%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodParamName%20%3CBR%20%2F%3E%20END%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20close%20out%20the%20method%20name%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndComplexType%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndElement%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20response%20message%20structure%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartElement%20%2B%20N'%22'%20%2B%20%40tMethodNameBak%20%2B%20N'Response%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartComplexType%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdStartElement%20%2B%20N'%22'%20%2B%20%40tMethodNameBak%20%2B%20N'Result%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndElement%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndComplexType%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndElement%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLOSE%20webMethodInfo_Cursor%20%3CBR%20%2F%3E%20DEALLOCATE%20webMethodInfo_Cursor%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40xsdEndSchema%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20NEXT%20FROM%20webMethodNS_Cursor%20INTO%20%40tMethodNS%20%3CBR%20%2F%3E%20END%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20CLOSE%20webMethodNS_Cursor%20%3CBR%20%2F%3E%20DEALLOCATE%20webMethodNS_Cursor%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20--%20close%20the%20%3CTYPES%3E%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlEndTypes%3C%2FTYPES%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20need%20to%20loop%20through%20each%20webmethod%20on%20the%20endpoint%20%3CBR%20%2F%3E%20declare%20webMethodInfo_Cursor%20SCROLL%20CURSOR%20FOR%20%3CBR%20%2F%3E%20SELECT%20DISTINCT%20webMethodName%2C%20webMethodNamespace%2C%20namespaceSuffix%20%3CBR%20%2F%3E%20from%20%23tempWSDLTable%20%3CBR%20%2F%3E%20ORDER%20BY%20webMethodNamespace%20ASC%2C%20webMethodName%20ASC%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Edeclare%20%40tIdSuffix%20int%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20open%20webMethodInfo_Cursor%20%3CBR%20%2F%3E%20FETCH%20FIRST%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodNS%2C%20%40tIdSuffix%20%3CBR%20%2F%3E%20WHILE%20%40%40FETCH_STATUS%20%3D%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20--%20the%20%3CMESSAGE%3E%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartMessage%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20N'SoapIn%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartPart%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'%3A'%20%2B%20%40tMethodName%20%2B%20N'%22%20%2F%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlEndMessage%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartMessage%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20N'SoapOut%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartPart%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'%3A'%20%2B%20%40tMethodName%20%2B%20N'Response%22%20%2F%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlEndMessage%20%3CBR%20%2F%3E%20FETCH%20Next%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodNS%2C%20%40tIdSuffix%20%3CBR%20%2F%3E%20END%3C%2FMESSAGE%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20the%20%3CPORTTYPE%3E%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartPortType%20%2B%20N'%22'%20%2B%20name%20%2B%20N'Soap%22%26gt%3B'%20%3CBR%20%2F%3E%20from%20sys.http_endpoints%20where%20endpoint_id%20%3D%20%40EndpointId%3C%2FPORTTYPE%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20FIRST%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodNS%2C%20%40tIdSuffix%20%3CBR%20%2F%3E%20WHILE%20%40%40FETCH_STATUS%20%3D%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20--%20the%20%3COPERATION%3E%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartOperation%20%2B%20N'%22'%20%2B%20%40tMethodName%20%2B%20N'%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartInput%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20%3CBR%20%2F%3E%20N'SoapIn%22%20message%3D%22tns%3As'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20N'SoapIn%22%20%2F%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartOutput%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20%3CBR%20%2F%3E%20N'SoapOut%22%20message%3D%22tns%3As'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20N'SoapOut%22%20%2F%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlEndOperation%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodNS%2C%20%40tIdSuffix%20%3CBR%20%2F%3E%20END%3C%2FOPERATION%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlEndPortType%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20the%20%3CBINDING%3E%20node%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartBinding%20%2B%20N'%22'%20%2B%20name%20%2B%20N'Soap%22%20type%3D%22tns%3A'%20%2B%20name%20%2B%20N'Soap%22%26gt%3B'%20%3CBR%20%2F%3E%20from%20sys.http_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%3C%2FBINDING%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40soapBinding%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFETCH%20FIRST%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodNS%2C%20%40tIdSuffix%20%3CBR%20%2F%3E%20WHILE%20%40%40FETCH_STATUS%20%3D%200%20%3CBR%20%2F%3E%20BEGIN%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartOperation%20%2B%20N'%22'%20%2B%20%40tMethodName%20%2B%20N'%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40soapStartOperation%20%2B%20N'%22'%20%2B%20%40tMethodNS%20%2B%20%40tMethodName%20%2B%20N'%22'%20%2B%20%40soapEndOperation%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartInput%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20N'SoapIn%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40soapBody%20%2B%20%40wsdlEndInput%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartOutput%20%2B%20N'%22s'%20%2B%20CAST(%40tIdSuffix%20as%20nvarchar(3))%20%2B%20N'Msg'%20%2B%20%40tMethodName%20%2B%20N'SoapOut%22%26gt%3B'%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40soapBody%20%2B%20%40wsdlEndOutput%20%2B%20%40wsdlEndOperation%20%3CBR%20%2F%3E%20FETCH%20NEXT%20FROM%20webMethodInfo_Cursor%20INTO%20%40tMethodName%2C%20%40tMethodNS%2C%20%40tIdSuffix%20%3CBR%20%2F%3E%20END%3C%2FP%3E%3CBR%20%2F%3E%3CP%3ECLOSE%20webMethodInfo_Cursor%20%3CBR%20%2F%3E%20DEALLOCATE%20webMethodInfo_Cursor%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlEndBinding%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartService%20%2B%20N'%22'%20%2B%20name%20%2B%20N'%22%26gt%3B'%20%3CBR%20%2F%3E%20from%20sys.http_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlStartPort%20%2B%20N'%22'%20%2B%20name%20%2B%20N'%22%20binding%3D%22tns%3A'%20%2B%20name%20%2B%20N'Soap%22%26gt%3B'%20%3CBR%20%2F%3E%20from%20sys.http_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eif%20(%40IsSSL%20%3D%201)%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40soapStartAddress%20%2B%20N'%22%20%3CA%20href%3D%22https%3A%2F%2F'%2F%22%20mce_href%3D%22https%3A%2F%2F'%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20https%3A%2F%2F'%20%3C%2FA%3E%20%2B%20%40Host%20%2B%20N'%2F'%20%2B%20url_path%20%2B%20N'%22%20%2F%26gt%3B'%20%3CBR%20%2F%3E%20from%20sys.http_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%20%3CBR%20%2F%3E%20end%20%3CBR%20%2F%3E%20else%20%3CBR%20%2F%3E%20begin%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40soapStartAddress%20%2B%20N'%22%20%3CA%20href%3D%22http%3A%2F%2F'%2F%22%20mce_href%3D%22http%3A%2F%2F'%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2F'%20%3C%2FA%3E%20%2B%20%40Host%20%2B%20N'%2F'%20%2B%20url_path%20%2B%20N'%22%20%2F%26gt%3B'%20%3CBR%20%2F%3E%20from%20sys.http_endpoints%20where%20endpoint_id%20%3D%20%40EndpointID%20%3CBR%20%2F%3E%20end%3C%2FP%3E%3CBR%20%2F%3E%3CP%3Eselect%20%40outputWSDL%20%3D%20%40outputWSDL%20%2B%20%40wsdlEndPort%20%2B%20%40wsdlEndService%20%2B%20%40wsdlEndDefinitions%20%3CBR%20%2F%3E%20end%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E--%20The%20WSDL%20document%20must%20be%20returned%20to%20the%20client%20using%20this%20GUID%20as%20the%20column%20name.%20%3CBR%20%2F%3E%20select%20%40outputWSDL%20as%20N'XML_F52E2B61-18A1-11d1-B105-00805F49916B'%20%3CBR%20%2F%3E%20end%20%3CBR%20%2F%3E%20go%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EJimmy%20Wu%2C%20SQL%20Server%20Protocols%20%3CBR%20%2F%3E%20Disclaimer%3A%20This%20posting%20is%20provided%20%E2%80%9CAS%20IS%E2%80%9D%20with%20no%20warranties%2C%20and%20confers%20no%20rights%3C%2FP%3E%0A%20%0A%3C%2FBINDING%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-383178%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Nov%2007%2C%202006%20We've%20gotten%20feedback%20from%20customers%20who%20have%20asked%20if%20it%20is%20possible%20to%20write%20a%20custom%20WSDL%20generator%20in%20T-SQL.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-383178%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerProtocols%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FDEFINITIONS%3E%3C%2FLINGO-BODY%3E
Microsoft
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