Execute SQL and Web Request Examples for Office 365

I am going to share configuration examples of Execute SQL and Web Request workflow actions for Office 365. The example I used is based on the previous post by Dan Stoll on Sign your name across my heart and my previous post on Extending Nintex Mobile Signature control to desktop/browser to demonstrate how the Nintex Signature Control example could be used for a Shipping or Delivery Order scenario of business process. I have simply modified the Title control to enable the barcode scanning feature, and renamed the label to Item ID as shown in the below setting of the Title (i.e. Single Line Textbox control).

The outcome of that is shown in the diagram below, which could be used as a Delivery Form to which a delivery man could use to scan the Bar code or QR Code of a parcel or item delivered, and get the consignee to sign the Delivery Form for confirming the parcel/items have been delivered.

Once the form data had been submitted and sync back to the Sharepoint on Office 365, a workflow will be triggered to send Delivery Tracking Status to inform delivery status over email and/or SMS. Two workflow actions will be used in this scenario to make the process complete:

1. Execute SQL action for querying the email and mobile number from SQL Server,
2. Web Request action for calling a web service provided by SMS Gateway service provide to send SMS message(s).

Here is how the Database table looks like, contains the Email and SMS registered for the Delivery Status Tracking purpose. The database in my example is in an on-premise environment, the Execute SQL will be querying from office 365 to the on-premise environment, you will need to make sure the SQL database server is reachable to your tenant from the cloud.

The "Send Delivery Tracking Status" workflow in my example starts with the Execute SQL action and its configuration as shown.

The Execute SQL asction required two mandatory parameters - Connection String and Query. For quick access to the returned results of the Query, you can specify a single column values you want to retrieve (i.e. specify in "Column to retrieve" parameter) from your Select statement, the values are saved to a collection variable (i.e. specified in "Retrieved column values" parameter - colEmail in my example). The parameter of "Results in XML" is used to store the returned results of the Query in array/collection of XML (i.e. I used colXML collection variable in my example to store the entire returned results from the SQL Statement).

The Web Request action in this example is necessary as we need to call a SMS Gateway function provided by third party to send SMS messages. The web service I used in my example is provided by Clickatell (i.e. www.clickatell.com) supports different Web API, one of them being SOAP API. As such I am using the Web Request action to call the SOAP web service for sending SMS message(s).

The WSDL Location provided by the service provider for accessing the web service is:

WSDL: http://api.clickatell.com/soap/document_literal/webservice?wsdl

This is what we need for the URL value of the Web Request action, but without the ?wsdl at the end of the URL. That is only needed when getting the schema.

The Method we going to use will be SOAP 1.2 as this is a SOAP API with supports of SOAP 1.2 standard provided by the SMS service provider.

The following sample request body was given for sending SMS messages.

<?xml version="1.0" encoding="UTF-8"?>
 <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://api.clickatell.com/soap/document_literal/webservice">
 <SOAP-ENV:Body><ns1:sendmsg>
 <api_id>123456
</api_id>
 <user>MyUserName
</user>
 <password>MyPassword
</password>
 <text>This is my message here!
</text>
 <to>2799900001
</to>
 <to>2799900002
</to>
 </ns1:sendmsg>
 </SOAP-ENV:Body>
 </SOAP-ENV:Envelope>

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

What we need is just to replace the parameter values and use it as the Body of the Web Request configuration as shown in the diagram below.

The completed workflow of my example is shown in the below workflow diagram, which performs two branches of State Machine,

1. State 1 is to send an Email, followed by
2. State 2 to send SMS via the Web Request action.

"Get Item from Collection" ① is to retrieve the first Email value from the collection variable of colEmail (i.e. retrieved values in the Execute SQL action), here is how the action is being configured. The Email value is stored in variable txtEmail in my case.

"Get Item from Collection" ②is to retrieve the first XML content from the XML collection (i.e. colXML collection variable), the result is stored in txtXML text variable, here shows how the action is being configured.

The above action will give us below XML that is stored in txtXML workflow variable.

"Query XML" ③is being configured to retrieve the Mobile number from the above XML content, and stored the value in txtMobile workflow variable.

That's all we need to build a Delivery Form and  Delivery Status Tracking Notification process. Hope this example gives a good reference on how the Execute SQL and Web Request actions of Nintex Workflow for Office 365 can be configured.