4.4 SQL Queries and External Integrations: REST and SOAP Consumption

Quick Answer: When Aggregates cannot express a query (subqueries, UNION, arbitrary SQL, or external databases), OutSystems exposes the SQL element inside Server Actions for hand-written, parameterized queries. For data outside the platform database, you consume REST APIs and SOAP web services through Service Studio's Consume REST and Consume SOAP wizards, which generate Structures and Server Actions; input parameters map to the request and output Structures hold the deserialized JSON or XML response.

The SQL Element

The SQL element is a node you drop into a Server Action. You write standard SQL — including SELECT, INSERT, UPDATE, DELETE, and DDL when the connection allows — using @ParameterName syntax for inputs. The result set maps to a Structure you define; each SELECT column must correspond to a Structure attribute by name and type. Unlike an Aggregate, SQL is not schema-aware visually: if you rename an entity attribute, the SQL text does not auto-update, so you must edit it manually.

When to choose SQL over an Aggregate: you need a subquery, WITH clause, or UNION; you need bulk writes (the SQL element supports multi-row inserts); you are querying an external database via a Connection (for example a legacy SQL Server registered in Service Center); or you need a SQL function not exposed in the Aggregate expression editor. For simple reads, always prefer an Aggregate — it is optimized, type-safe, and easier to maintain.

Parameter and Output Mapping in SQL

Input parameters are declared in the SQL element's Parameter list and referenced in the text as @ParamName. The platform escapes and type-checks them, preventing SQL injection — never concatenate user input into the SQL string directly, as that bypasses the protection and introduces a security hole. Output is mapped through the Output Structure you select; the SELECT column names must match the Structure attribute names exactly (case-insensitive in most setups, but matching is safest). If a column has no matching Structure attribute, the platform raises a mapping error at runtime or silently drops it depending on configuration.

Consuming REST APIs

To consume a REST API in OutSystems O11, you use the Consume REST API wizard (right-click a module, then Consume REST API). You paste a sample JSON or a URL, and the wizard generates a Structure matching the JSON response shape (nested objects become nested Structures) and one Server Action per REST method you expose, with input parameters from the URL path, query string, and body, and an output parameter typed to the response Structure.

The generated Server Action calls the endpoint, receives the JSON, and deserializes it into the typed Structure using OutSystems' built-in JSON deserialization. You then access fields as normal Structure attributes (for example GetCustomer.List.Current.Name). No manual JSON parsing is required for typical cases. A key gotcha: the wizard infers the Structure from the sample JSON, so if the API later adds fields, you must re-run the wizard — the Structure does not auto-update.

Consuming SOAP Web Services

For SOAP, you use Consume SOAP Web Service and supply the WSDL URL. OutSystems generates a Structure for each complex type in the WSDL, a Server Action for each operation with input and output parameters typed to those Structures, and handles the SOAP envelope, namespace handling, and XML-to-Structure deserialization automatically.

A SOAP-specific gotcha: namespaces in the WSDL are preserved in the generated Structures; mismatched namespaces cause silent deserialization failures where fields come back empty even though the response was received.

JSON Deserialization

For cases where you receive raw JSON outside the Consume REST flow (for example from a custom HTTP request via the HTTPRequest server action or from a webhook payload), OutSystems provides the JSON Deserialize built-in function. You pass the JSON text and a Structure type; the function returns a typed record. The reverse — JSON Serialize — converts a Structure to JSON text. These are the same primitives the Consume REST wizard uses internally.

Common exam-relevant facts: the Consume REST wizard infers the Structure from sample JSON, so if the API later adds fields, you must re-run the wizard or extend the Structure manually. Output Structures are plain data containers with no behavior and no entity actions. REST methods are exposed as Server Actions, callable from any Server Action flow but not directly from a Client Action without a wrapper. SOAP namespaces in the WSDL are preserved in the generated Structures; mismatched namespaces cause silent deserialization failures.