Share via

  • Article

SOAP Response Message Structure

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

This topic describes the SOAP response message structure if you want to parse the SOAP response instead of using the proxy classes provided by Visual Studio 2005.

A SOAP response message that is returned by an instance of SQL Server may include one or more of the following:

  • Results set for SELECT queries.

  • Return code for stored procedures and user-defined functions.

  • Row count. This is the number of rows affected by the query.

  • Output parameter values.

  • Error messages or warnings.

The following is a fragment of the SOAP response structure that is returned by SQL Server after a stored procedure is run:

<?xml version="1.0" encoding="utf-8" ?> 
<SOAP-ENV:Envelope xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
  ...>
  <SOAP-ENV:Body>
  <method:MethodNameResponse>
  <method:MethodNameResult  
 xsi:type="sqlresultstream:SqlResultStream">
<!-- 
  the results are returned here
-->
  </method:MethodNameResult>
  <method:OutputParam>Value</method:OutputParam> 
  </method:MethodNameResponse>
  </SOAP-ENV:Body>
  </SOAP-ENV:Envelope>

The results of an operation are wrapped in the <MethodNameResponse> element in the SOAP body. The <MethodNameResponse> element may include the following child elements:

  • <MethodNameResult> includes the results, the row count values, and error messages and warnings.

  • <OutputParameter> includes output parameters, if any, returned by stored procedures.

<MethodNameResult> Element

The results of an operation are returned inside the <MethodNameResult> element where MethodName is either the name of a stored procedure or user-defined function or is sqlbatch for ad hoc batch queries. sqlbatch is the method used to run ad hoc queries.

Inside the <MethodNameResult> element the results are serialized as follows:

  • The result of a SELECT statement is wrapped in a <SqlRowSet> element. The results serialized in the <SqlRowSet> element use DiffGram serialization format. The DiffGram format is introduced in the DataSet component of the Microsoft .NET Framework. In this case, the response also includes an XSD schema (if the endpoint is created with schema enabled) before the data. In Visual Studio 2005 client, the results are returned as System.Data.DataSet objects.

    Note

    To load SOAP results into System.Data.DataSet objects when you are using Visual Studio 2005, SQL Server must include inline schemas in SOAP responses that it returns to clients. This behavior is enabled for endpoints by either omitting the SCHEMA keyword or by specifying SCHEMA=STANDARD when you create the endpoint. For more information, see CREATE ENDPOINT (Transact-SQL).

  • The result of a SELECT... FOR XML statement is wrapped in a <SqlXml> element. If multiple result sets are returned (for example, a stored procedure with multiple SELECT...FOR XML queries), each result set is wrapped in its own <SqlXml> element. Also, each result is followed by a <SqlRowCount> element. This element returns the number of rows affected by the query, as shown in the following response fragment:

    <tns:MethodNameResponse>
  <tns:MethodNameResult 
xsi:type="sqlsoaptypes:SqlResultStream">
     <sqlresultstream:SqlXml xsi:type="sqlsoaptypes:SqlXml" >
 <SqlXml>
    <!-- XML result --> 
 </SqlXml>
</sqlresultstream:SqlXml>
<sqlresultstream:SqlRowCount xsi:type="sqlrowcount:SqlRowCount">
   <sqlrowcount:Count>NoOfRowsAffected</sqlrowcount:Count> 
</sqlresultstream:SqlRowCount>
<sqlresultstream:SqlXml xsi:type="sqlsoaptypes:SqlXml" >
  <SqlXml>
    <!-- XML Document that maps to XMLElement object 
   in the client--> 
  </SqlXml>
</sqlresultstream:SqlXml>
<sqlresultstream:SqlRowCount xsi:type="sqlrowcount:SqlRowCount">
   <sqlrowcount:Count>NoOfRowsAffected</sqlrowcount:Count> 
</sqlresultstream:SqlRowCount>

     <!-- more results ...-->
  </tns:MethodNameResult>
  <tns:OutputParam>Value</tns:OutputParam> 
</tns:MethodNameResponse>

    If the SELECT... FOR XML query requests an XSD schema by specifying the XMLSCHEMA option, the XSD schema is returned as the first child element of the <SqlXml> element.

    In Visual Studio 2005 client, the results are returned as System.Xml.XmlElement objects.

  • The return code values from stored procedures and user-defined functions are wrapped in a <SqlResultCode> element, as shown in the following response fragment:

    <tns:MethodNameResponse>

      <tns:MethodNameResult 
xsi:type="sqlsoaptypes:SqlResultStream">
  <!-- 
    results 
  -->
     <sqlresultstream:SqlResultCode
xsi:type="sqlsoaptypes:SqlResultCode">
   ReturnCodeValue
     </sqlresultstream:SqlResultCode> 
  </tns:MethodNameResult>
  <tns:OutputParam>Value</tns:OutputParam> 
</tns:MethodNameResponse>

    In Visual Studio 2005 client, the return code is returned as an object.

  • SQL Server error messages, warnings, and other informational messages are wrapped in a <SqlMessage> element, as shown in the following SOAP response fragment:

    <tns:MethodNameResponse>
  <tns:MethodNameResult xsi:type="sqlresultstream:SqlResultStream">
     <sqlresultstream:SqlMessage>
    <!-- Error message returned as SqlMessage object 
 in the client --> 
</sqlresultstream:SqlMessage>
 ...
  </tns:MethodNameResult>
  <!-- followed by one or more return parameters ->
   ...
</tns:MethodNameResponse>

    The following SOAP response fragment is returned by running the GetCustomerInfo stored procedure. For information about this stored procedure, see Sample Applications for Sending Native XML Web Services Requests. The first INSERT statement in the stored procedure fails. This causes the following response.

    <method:GetCustomerInfoResponse>
 <method:GetCustomerInfoResult 
    xsi:type="sqlresultstream:SqlResultStream">
    <sqlresultstream:SqlMessage>
     <sqlresultstream:SqlMessage xsi:type="sqlmessage:SqlMessage">
<sqlmessage:Class>16</sqlmessage:Class> 
<sqlmessage:LineNumber>12</sqlmessage:LineNumber> 
<sqlmessage:Message>Cannot insert the value NULL into column 
'CompanyName', table 'Northwind.dbo.Customers'; 
column does not allow nulls. INSERT fails. 
</sqlmessage:Message> 
     <sqlmessage:Number>515</sqlmessage:Number> 
     <sqlmessage:Procedure>GetCustomerInfo</sqlmessage:Procedure> 
     <sqlmessage:Server>SQLServerName</sqlmessage:Server> 
     <sqlmessage:Source>MicrosofTransact-
SQL/9.0</sqlmessage:Source> 
     <sqlmessage:State>2</sqlmessage:State> 
    </sqlresultstream:SqlMessage>
    <sqlresultstream:SqlRowCount xsi:type="sqlrowcount:SqlRowCount">
 <sqlrowcount:Count>0</sqlrowcount:Count> 
    </sqlresultstream:SqlRowCount> 
    <sqlresultstream:SqlResultCode 
  xsi:type="sqlsoaptypes:SqlResultCode">-6 
    </sqlresultstream:SqlResultCode> 
  </method:GetCustomerInfoResult>
  <!-- followed by one or more output parameters ->
 </method:GetCustomerInfoResponse>
</tns:MethodNameResponse>

    In Visual Studio 2005 client, these results are returned as an SqlMessage type objects.

<OutputParameter> Element

Each output parameter value is returned wrapped in an element. The element name is the name of the output parameter. Note that this element appears after the <MethodNameResult> element as the last child of the <MethodNameResponse> element; this is shown in the following SOAP response fragment. Two output parameters are returned.

<tns:MethodNameResponse>
   <tns:MethodNameResult xsi:type="sqlresultstream:SqlResultStream">
     <!-- results, error messages and warnings -->
   </tns:MethodNameResult>
   <tns:OutputParam1>Value</tns:OutputParam1> 
   <tns:OutputParam2>Value</tns:OutputParam2> 
</tns:MethodNameResponse>