Building a WebMethod FORMAT=NONE Stored Procedure

  • Article

Commonly for Web Services, the exposed interfaces (webmethods) must conform to some pre-defined contract.  To assist customers developing web services which conforms to these contracts, SQL Server 2005 Native Web Services provides the ability for customers to construct and format the output XML themselves.  To do this, the Stored Procedure (or CLR Stored Procedure) must return only one column of type 'nvarchar' (any length including "max") with the column name of "XML_F52E2B61-18A1-11d1-B105-00805F49916B".

For example:
CREATE PROC spTestFormat
AS
  DECLARE @x XML
  SET @x = N'<hello xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"><!-- The main thing to remember when using this workaround is that there can only be one column, the column type must be "nvarchar" and the column name must be "XML_F52E2B61-18A1-11d1-B105-00805F49916B".--><foo><nested1><nullNode xsi:nil="true" /></nested1></foo><bar>You can specify any thing you like as long as it is valid XML.</bar></hello>'
  SELECT convert(nvarchar(max), @x) as 'XML_F52E2B61-18A1-11d1-B105-00805F49916B'
GO

When exposing the Stored Procedure as a WebMethod on the endpoint, remember to set the FORMAT keyword to the value of "NONE".

For example:
CREATE ENDPOINT epTestFormat
  STATE=STARTED
AS HTTP (
  ...
)
FOR SOAP
(
  WEBMETHOD 'https://tempuri.org'.'testFormat' (name='master.dbo.spTestFormat', FORMAT=NONE)
)

The resulting SQL Server output response for this Web Method will be:
<?xml version="1.0" encoding="utf-8"?>
<hello xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">
  <!-- The main thing to remember when using this workaround is that there can only be one column, the column type must be "nvarchar" and the column name must be "XML_F52E2B61-18A1-11d1-B105-00805F49916B".-->
  <foo>
    <nested1>
      <nullNode xsi:nil="true" />
    </nested1>
  </foo>
  <bar>You can specify any thing you like as long as it is valid XML.</bar>
</hello>

The combination of this functionality of customizing the Stored Procedure result format with the Custom WSDL generation capability (see https://blogs.msdn.com/sql_protocols/archive/2006/11/07/building-t-sql-custom-wsdl-generator.aspx), users will be able to develop web services that fully describe the exact interface and to conform to any interface the organization defines.

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