Home/DriveWorks guides/Calling web services from DriveWorks: HTTP requests, JSON, and XML
Calling web services from DriveWorks: HTTP requests, JSON, and XML
Use the Send HTTP Request task to call a web service from a DriveWorks specification macro and store the response body in a constant. Read JSON with XPathJsonGet and XML with SppXmlGetElementValue. The same three steps connect DriveWorks to ERPs, CRMs, carriers, and mapping services.
The three steps
Almost every web service integration in DriveWorks has the same shape:
- Build the request with rules: the URL, headers, and body.
- Send it with the Send HTTP Request task in a specification macro. The task's Response Body Constant Name property names the constant that receives the response.
- Read the response with rules, picking out the values you need.
Web service integration is a DriveWorks Pro feature.
Reading JSON
XPathJsonGet finds the value at a path in a JSON string:
XPathJsonGet(DWConstantResponseBody, "books[0].title")
Arrays are zero-based. The first element is [0], which matters as soon as you loop.
Take a response that is a list of rows:
{
"T": [
{ "H1": "V11", "H2": "V12", "H3": "V13" },
{ "H1": "V21", "H2": "V22", "H3": "V23" }
]
}
XPathJsonGet(DWConstantResponseBody, "T[0].H1") returns V11. "T[1].H3" returns V23.
Turning a JSON list into a table
In current versions the shortest route is the Specification PowerPack function SppConvertJsonToTable, which converts a JSON array or object straight into a DriveWorks table. Tables are two-dimensional, so anything nested a level deeper comes back as a string in its cell, and invalid JSON returns an error string instead of a table.
When you need a rule per column, or the JSON is nested, pull it into a Calculation Table instead.
Make the first column an ID. Give the first cell a rule that is the hard-coded number 1, and give the column the rule [1U] + 1. Each row is then one more than the row above, which gives a counter to index with.
Use the ID in the other columns. Each remaining column reads one field, referring to the ID column with [1L] and subtracting one because JSON arrays start at zero:
XPathJsonGet(DWVariableDataJSON, "T[" & [1L] - 1 & "].H1")
A caution from experience: Calculation Tables are convenient and I use them for a lot of things, but large ones can cause serious performance problems. For a one-off import this is fine. For something evaluated on every form change, keep the table small or do the work in a macro.
Reading XML
For XML responses, SppXmlGetElementValue from the Specification PowerPack returns the value of one element. Its input is an XML object, such as the result of SppXmlLoad or SppXmlFromString.
Element paths use a backslash, for example Quote\Customer, and an instance number picks one of several elements with the same name: Quote\Items\Item\6 is the sixth item.
If an API returns JSON but your existing rules expect XML, or the other way round, the PowerPack also has SppJSONToXml and SppXmlToJson. For JSON that arrives as a file instead of a response, SppJsonLoad reads a .json file into a string.
A worked example: distance from a list of leads
The job: a list of leads arrived as JSON with latitude and longitude. I wanted the driving distance from Petoskey, Michigan to each one, saved to a SQL table so the list could be sorted by distance.
1. Load the leads into a Calculation Table using the ID column pattern above.
2. Loop over the rows. For each row, a rule builds the request URL for the Google Maps Distance Matrix API from that row's coordinates:
"https://maps.googleapis.com/maps/api/distancematrix/xml?origins=Petoskey, MI&destinations="
& TableGetValue(DWCalcJSON, TableGetColumnIndexByName(DWCalcJSON, "location_lt", TRUE), DWConstantLoopIndex)
& "," & TableGetValue(DWCalcJSON, TableGetColumnIndexByName(DWCalcJSON, "location_lng", TRUE), DWConstantLoopIndex)
& "&units=metric&key="
& DWConstantGoogleMapsAPIKey
Keep the API key in a constant. Do not paste it into the rule.
3. Send the request with Send HTTP Request. The response is XML containing the origin, the destination, a duration, and a distance.
4. Read the distance:
SppXmlGetElementValue(DWConstantMapsResponseBody, "DistanceMatrixResponse\row\element\distance\value")
The value is in meters. The same response also holds the travel time.
5. Write it back to the SQL table with DbExecute.
The example is small, but nothing about it is specific to maps. Replace the URL with your ERP's item endpoint or a carrier's rating API and the steps are identical.
Practical notes
- Check the status before the body. The task has three status outputs. Success means the request was sent. Success with Warnings includes the case where the request went through but the server returned an error, so a 500 from the other system does not arrive as a failure. Failed covers a bad URL, verb, or body. Branch the macro on the status and log the body when it is not a clean success. An error page parsed as data produces confusing rules.
- Keep secrets out of rules. API keys and tokens belong in constants or a secured table.
- Headers are one string. Request Headers is a pipe-delimited list of
name:valuepairs, for example"content-type:application/json | Accept:application/json". - GET and HEAD send no body. Anything in Request Body is dropped for those verbs.
- Mind the rate limits. A loop that fires one request per row can hit a provider's limit quickly.
- It runs unattended. Send HTTP Request works on Autopilot, so a released specification can call an API with nobody at the keyboard. See handing jobs to Autopilot.
- Do not build what you can buy. I wrote a small CRM this way once, to learn. Do not do that. Use the technique to connect to the systems you already have. DriveWorks ERP integration covers the ones Base 10 has shipped.
Common questions
Can DriveWorks call a REST API?
Yes. DriveWorks Pro has a Send HTTP Request task that sends a request from a specification macro and stores the response body in a constant for rules to read.
How do I parse JSON in DriveWorks?
Use the XPathJsonGet function with the JSON string and a path, for example XPathJsonGet(data, "books[0].title"). Array indexes start at zero.
How do I read XML returned by a web service in DriveWorks?
Use SppXmlGetElementValue from the Specification PowerPack to return the value of a single element. It takes an XML object, such as the result of SppXmlLoad or SppXmlFromString.
Can DriveWorks Autopilot make HTTP requests unattended?
Yes. The Send HTTP Request task runs on Autopilot, so released specifications can call outside APIs without a user present.
Can DriveWorks convert between JSON and XML?
Yes. The Specification PowerPack includes SppJSONToXml and SppXmlToJson.
Related
Base 10 Consultants is a DriveWorks Authorized Services Partner: CPQ configurators, design automation, and CAD-to-ERP integration for manufacturers.
Book a 30-minute callBase 10 Consultants is a DriveWorks Authorized Services Partner and an independent company. This page is not written, reviewed, or endorsed by DriveWorks Ltd. DriveWorks is a trademark of DriveWorks Ltd; SOLIDWORKS is a trademark of Dassault Systèmes SolidWorks Corp. Behavior described here can differ between versions, so check the DriveWorks documentation for the version you run.