Web service client calls can be made with either stored functions, or stored procedures. If made from a function, the return type of the function must be of a character data type, such as CHAR, VARCHAR, or LONG VARCHAR. The value returned is the body of the HTTP response. No header information is included. Additional information about the request, including the HTTP status information, is returned by procedures. Thus, procedures are preferred when access to this additional information is desired.
The response from a SOAP function is an XML document that contains the SOAP response.
Since SOAP responses are structured XML documents, SQL Anywhere by default attempts to exploit this information and construct a more useful result set. Each of the top-level tags within the returned response document is extracted and used as a column name. The contents of the subtree below each of these tags is used as the row value for that column.
For example, given the SOAP response shown below, SQL Anywhere would construct the shown data set:
<SOAP-ENV:Envelope xmlns:SOAPSDK1="http://www.w3.org/2001/XMLSchema" xmlns:SOAPSDK2="http://www.w3.org/2001/XMLSchema-instance" xmlns:SOAPSDK3="http://schemas.xmlsoap.org/soap/encoding/" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"> <SOAP-ENV:Body> <ElizaResponse xmlns:SOAPSDK4="SoapInterop"> <Eliza>Hi, I'm Eliza. Nice to meet you.</Eliza> <ElizaResponse> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
|Hi, I'm Eliza. Nice to meet you.|
In this example, the response document is delimited by the ElizaResponse tags that appear within the SOAP-ENV:Body tags.
Result sets have as many columns as there are top-level tags. This result set has only one column because there is only one top-level tag in the SOAP response. This single top-level tag, Eliza, becomes the name of the column.
Information within XML result sets, including SOAP responses, can also be accessed using the built-in Open XML processing capabilities.
The following example uses the OPENXML procedure to extract portions of a SOAP response. This example uses a web service to expose the contents of the SYSWEBSERVICE table as a SOAP service:
CREATE SERVICE get_webservices TYPE 'SOAP' AUTHORIZATION OFF USER DBA AS SELECT * FROM SYSWEBSERVICE;
The following stored function, which must be created in a second SQL Anywhere database, issues a call to this web service. The return value of this function is the entire SOAP response document. The response is in the .NET DataSet format, as DNET is the default SOAP service format.
CREATE FUNCTION get_webservices() RETURNS LONG VARCHAR URL 'HTTP://localhost/get_webservices' TYPE 'SOAP:DOC';
The following statement demonstrates how two columns of the result set can be extracted with the aid of the OPENXML procedure. These are the service_name and secure_required columns, which indicate which SOAP services are secure and hence require HTTPS.
SELECT * FROM openxml( get_webservices(), '//row' ) WITH ("Name" char(128) 'service_name', "Secure?" char(1) 'secure_required' );
This statement works by selecting the decedents of the row node. The WITH clause constructs the result set based on the two elements of interest. Assuming only the get_webservices service exists, this function returns the following result set:
For more information about the XML processing facilities available in SQL Anywhere, see Using XML in the Database.
Procedures of other types return all the information about a response in a two-column result set. This result set includes the response status, header information and body. The first column, is named Attribute and the second Value. Both are of data type LONG VARCHAR.
The result set has one row for each of the response header fields, as well as a row for the HTTP status line (Status attribute) and a row for the response body (Body attribute).
The following example represents a typical response:
|Status||HTTP /1.0 200 OK|
|Body||<!DOCTYPE HTML ... ><HTML> ... </HTML>|
|Date||Mon, 18 Oct 2004, 16:00:00 GMT|