This tied together all the SOAP aspects of the call - the endpoint, the operation, and the argument list. To test the SOAP side of the call (and see if anything came back) I simply called this from iSQL.

I knew that iSQL wouldn't display any result set; the stored procedure is only returning the SOAP payload as a single LONG VARCHAR, not a result set. Why did I try this knowing that iSQL couldn't display anything? I knew that I'd be looking into the HTTP log file and seeing what was generated by SQL Anywhere for the SOAP call and then what was returned by the remote server. I wanted to get over that small hurdle before tackling the decomposition of the returned data payload.

call GetUPSRate( 'SHP-M89872906F', '',
'60506', 'US',
'01776', 'US',
'10.5', '0' )

The first two fields are the trial user information, which changes every week or so. The second pair of fields are the ZIP code and country for Aurora, IL (60506), the next pair of fields are the ZIP code and country for Sudbury, MA (01776), and the final two fields are the package weight and value.

Looking in the HTTP log file for the database server, I see that this trial Web Service call succeeded and reasonable looking data was returned (see Listing 1).

Examining this response data confirmed my understanding from the eCoComa Web site. Basically, I wanted to extract the 'Code,' 'Rate,' and 'Mail Service' values as columns repeated for each "Postage" row in my result set.

The next step was to figure out the syntax for the call to the SQL Anywhere 10 system procedure "openxml" to generate rows of data with my desired columns. Wrapping my head around the XPath query string and namespace qualifiers consumed many lunch hours, but reading and rereading the iAnywhere documentation for the openxml system procedure, the iAnywhere newsgroups, and the online resources about XPath led me to the following simple select statement using openxml.

SELECT MailCode,Rate,Service
FROM openxml(
      GetUPSRate( ... arguments ... ),
'//*:Postage', 2 )
WITH ( MailCode CHAR(10) '*:Code',
Rate CHAR(10) '*:Rate',
Service LONG VARCHAR '*:MailService' )

The '//*:Postage' is the XML node that generates a matching row in the result set.

The WITH statement provides the correlation between the XML nodes in the SOAP payload and what I wish to use as columns in my result set.

Wrapping all this up, I created a second stored procedure (using the naming ideas from Chance's article):

call sa_make_object( 'procedure', 'GetUPSRateWrapperUS');

alter procedure GetUPSRateWrapperUS(
@MemberKey char(20),
@SourceZip char(20),
@DestZip char(20),
@Weight char(10) )
begin
SELECT MailCode,Rate,Service FROM openxml(
      GetUPSRate( @MemberKey , '',
      @SourceZip, 'US',
      @DestZip, 'US',
      @Weight, '0' ),
'//*:Postage', 2 )
WITH ( MailCode CHAR(10) '*:Code',
Rate CHAR(10) '*:Rate',
Service LONG VARCHAR '*:MailService' )
end;

As you can see, I hard-coded for shipping in the U.S., but it can easily be extended for international shipping. I'd just need to pass in the value for customs and the correct country codes (and maybe replace the phrase 'Zip' with the more international 'PostalCode').