Quite recently I was working for a project that needed Salesforce data from cloud back to on premise SQL.
Salesforce Rest Apis enable working with Queries and a SOQL query can be executed under Query resource that returns all the results in a single response, or if needed, returns part of the results and an identifier used to retrieve the remaining results.
Rest Api Call: https://yourInstance.salesforce.com/services/data/v20.0/query/?q=SELECT+name+from+Account -H “Authorization: Bearer token”
As we can see there the SOQL needs to have the field name that we want and a select * is not supported by SOQL.
So the requirement was to fetch all the data, that could have been achieved by specifying all the field names on Query but then the solution would be dependent on Salesforce updates and modification.
The next approach is to query the available fields from Salesforce and dynamically create the query to execute.
Rest Api :
The resultant xml is like:
Now we can iterate over all the <fields> and get all field names for our SOQL query.
And then build the “Select GetAllFieldsQuery(describe Xml) from Account”
This way for any changes to Salesforce object, the interface doesnot need to change and is not prone to breaks or unexpected result because the results are laways in sync on Salesforce cloud and On premise SQL.
The Rest Api calls are easily done via BizTalk 2013R2 Web-Http adapter. The default response for Api calls is Json and BizTalk 2013R2 is fully equiped with tools to handle Json messages. If however, we intend to have the response as xml, this can be achived by setting Http Header – Accept : application/xml