/* -- SOAP client procedure --
    uses substitution parameters to send SOAP headers
         a single inout parameter is used to receive SOAP headers
*/
create or replace procedure soapAddItemProc("amount" int, item long varchar, inout inoutheader long varchar, in inheader long varchar )
	url 'http://localhost/store'
    set 'SOAP( OP=addItems )'
    type 'SOAP:DOC'
	soapheader '!inoutheader!inheader';

/* Wrapper that calls soapAddItemProc
    demonstrates:
        how to send and receive soap headers
        how to send parameters
 */
create or replace procedure addItemProcWrapper( amount int, item long varchar, "first" long varchar, "last" long varchar )
    begin
        declare io_header long varchar;	// inout (write/read) soap header
        declare resxml long varchar;
        declare soap_header_sent long varchar;
        declare i_header long varchar;	// in (write) only soap header
        declare err int;
        declare crsr cursor for call soapAddItemProc( amount, item, io_header, i_header );

        set io_header = XMLELEMENT( 'Authentication',
			      	XMLATTRIBUTES('CustomerOrderURN' as xmlns),
                    XMLELEMENT('userName', XMLATTRIBUTES(
				            'none' as pwd,
				            '1' as mustUnderstand ),
                        	XMLELEMENT( 'first', "first" ),
                        	XMLELEMENT( 'last', "last" ) ) );
        set i_header = '<Session xmlns="SomeSession">123456789</Session>';
        set soap_header_sent = io_header || i_header;
        open crsr;
        fetch crsr into resxml, err;
        close crsr;

        select resxml, err, soap_header_sent, io_header as soap_header_received;
    end;

/* example call to addItemProcWrapper */
call addItemProcWrapper( 5, 'shirt', 'John', 'Smith' );


/* -- SOAP client function --
    uses substitution parameters to send SOAP headers
    Entire SOAP response envelope is returned.  SOAP headers can be parsed using openxml
*/
create or replace function soapAddItemFunction("amount" int, item long varchar, in inheader1 long varchar, in inheader2 long varchar )
returns XML
	url 'http://localhost/store'
    set 'SOAP( OP=addItems )'
    type 'SOAP:DOC'
	soapheader '!inheader1!inheader2';

/* Wrapper that calls soapAddItemFunction
    demonstrates the use of SOAP function:
        how to send and receive soap headers
        how to send parameters and process response
 */
create or replace procedure addItemFunctionWrapper( amount int, item long varchar, "first" long varchar, "last" long varchar )
    begin
        declare i_header1 long varchar;
        declare i_header2 long varchar;
        declare res long varchar;
        declare ns long varchar;
	    declare xpath long varchar;
	    declare header_entry long varchar;
	    declare localname long varchar;
	    declare namespaceuri long varchar;
        declare r_quantity int;
        declare r_item long varchar;
        declare r_status long varchar;
        

        set i_header1 = XMLELEMENT( 'Authentication',
			      	XMLATTRIBUTES('CustomerOrderURN' as xmlns),
                    XMLELEMENT('userName', XMLATTRIBUTES(
				            'none' as pwd,
				            '1' as mustUnderstand ),
                        	XMLELEMENT( 'first', "first" ),
                        	XMLELEMENT( 'last', "last" ) ) );
        set i_header2 = '<Session xmlns="SessionURN">123456789</Session>';

        set res = soapAddItemFunction( amount, item, i_header1, i_header2 );

	     set ns = '<ns xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"'
		|| ' xmlns:mp="urn:ianywhere-com:sa-xpath-metaprop"'
        || ' xmlns:customer="CustomerOrderURN"'
        || ' xmlns:session="SessionURN"'
		|| ' xmlns:tns="http://localhost"></ns>';

        // Process headers...	     
        set xpath = '//SOAP-ENV:Header/*';
	    begin
	     declare crsr cursor for select * from
		     openxml( res, xpath, 1, ns )
                          with (  "header_entry" long varchar '@mp:xmltext',
        				"localname" long varchar '@mp:localname',
        				"namespaceuri" long varchar '@mp:namespaceuri' );
            open crsr;
            fetch crsr into "header_entry", "localname", "namespaceuri";
            close crsr;
        end;

        // Process body...
        set xpath = '//tns:row';
	    begin
	     declare crsr1 cursor for select * from
		     openxml( res, xpath, 1, ns )
                          with (  "r_quantity" int 'tns:quantity/text()',
        				"r_item" long varchar 'tns:item/text()',
        				"r_status" long varchar 'tns:status/text()' );
            open crsr1;
            fetch crsr1 into "r_quantity", "r_item", "r_status";
            close crsr1;
        end;

    select r_item, r_quantity, r_status, header_entry, localname, namespaceuri;
    end;

/* example call to addItemFunctionWrapper */
call addItemFunctionWrapper( 5, 'shirt', 'John', 'Smith' );

/*
 Demonstrate how a HTTP:POST can be used as a transport for a SOAP payload
 Rather than creating a webservice client SOAP procedure, this approach
 creates a webservice HTTP procedure that transports a SOAP payload
*/
create or replace function httpAddItemFunction("soapPayload" xml )
returns XML
	url 'http://localhost/store'
    type 'HTTP:POST:text/xml'
    header 'SOAPAction: "http://localhost/addItems"';


/* Wrapper that calls soapAddItemFunction
    demonstrates the use of SOAP function:
        how to send and receive soap headers
        how to send parameters and process response
 */

create or replace procedure addItemHttpWrapper( amount int, item long varchar )
result(response xml)
begin
    declare payload xml;
    declare response xml;

    set payload =
'<?xml version="1.0"?>
<SOAP-ENV:Envelope
  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:m="http://localhost">
  <SOAP-ENV:Body>
    <m:addItems>
      <m:amount>' || amount || '</m:amount>
      <m:item>' || item || '</m:item>
    </m:addItems>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
    set response = httpAddItemFunction( payload );
    /* process response as demonstrated in addItemFunctionWrapper */
    select response;
end
go

/* example call to addItemHttpWrapper */
call addItemHttpWrapper( 5, 'shirt' );