Stream large nested JSON and XML with serialized nested elements

Overview

Starting with Etlworks 10.0.0, JSON and XML sources can be read in a fully streaming mode that preserves nested elements by serializing them in their native format. Each top-level record becomes one flat row: scalar values become regular columns, and every nested object or array becomes a string column containing the nested fragment as JSON (or XML).

This combines two things that previously worked against each other:

• Keeping the nested payload intact — the nested structure travels through the flow as a native-format string, ready to be stored in a JSON/VARIANT column or parsed later.
• True streaming — records are read one at a time, so memory depends on the size of the largest record, not the size of the file or response. Very large documents can be streamed straight to the destination.

It is especially powerful for JSON when combined with the Start Node, which selects the array to stream from inside a larger document (for example data.items), and with the HTTP connector's streaming response options for paginated APIs.

How it works

With full streaming read enabled, the connector reads the source as a sequence of records instead of loading the whole document:

• For JSON, the records are the elements of the source array (or the single object). With a Start Node such as data.items, the records are the elements of that node.
• For XML, the records are the direct children of the document root, or the elements selected by the Streaming record path.
• Scalar fields become columns. Nested objects and arrays are serialized to strings in the native format — JSON fragments for JSON sources, XML fragments for XML sources — using the existing formatting rules.
• The flow processes and loads each record as it is read. Memory is bounded by the largest single record, not the document size.

Example. This JSON source:

{
  "data": {
    "items": [
      {"id": 1, "status": "new",
       "customer": {"id": 7, "name": "Alice"},
       "lines": [{"sku": "A-1", "qty": 2}, {"sku": "A-2", "qty": 1}]}
    ]
  }
}

read with Full streaming read and Start Node data.items produces flat rows with these columns:

The customer and lines columns are ordinary string values containing valid JSON.

Enable it for JSON

Step 1. Open the source JSON (or API JSON) format.

Step 2. Enable Full streaming read.

Step 3. Optionally set the Start Node to a simple dotted path (for example data.items) to select the array to stream. Leave it empty to stream the top-level array or object.

Notes:

• Flows switch to streaming automatically when the option is enabled — no additional transformation settings are needed, and flat source metadata is returned automatically.
• The Start Node must be empty or a simple dotted path. The first matching path in the document is used. JavaScript Start Node expressions and document Preprocessors are not supported in this mode and are rejected with a configuration error.
• Sorting, nested mappings, and a local Source query can still prevent streaming — the data is read the same way, but the flow may need to materialize it for those operations.
• Because the document is processed incrementally, malformed content after the selected records can fail the flow after earlier rows have already reached the destination. There is no rollback of delivered rows.

Enable it for XML

Step 1. Open the source XML format.

Step 2. Enable Full streaming read.

Step 3. Optionally set the Streaming record path — a slash-separated path of exact qualified element names (for example response/items/item). Leave it empty to treat the direct children of the document root as records.

Notes:

• The record path is an element path with exact qualified names (including namespace prefixes as they appear in the document) — it is not XPath.
• Nested elements become XML string columns. Repeated sibling elements within a record are grouped into a single xml-fragments string.
• With Parse Attributes enabled, record attributes become @name columns.
• Whole-document transformations — XSLT, XQuery, preprocessing — are not supported in this mode, and DTDs and external entities are rejected.
• A column-name clash within a record is an error.

Stream API responses end to end

Two HTTP connection options extend the same approach to APIs (both are opt-in, under Output and Formatting):

• Stream JSON Response — reads the response incrementally using the first match of the Data JSON Path (a JSON Pointer such as /data/items) and, with automatic pagination, requests the next page only when the current one is consumed. Combine it with the JSON format's Full streaming read for an end-to-end streamed, flat pipeline: large multi-page API results never have to fit in memory.
• Stream CSV Response — for connections with Output as CSV: converts the JSON response to CSV record by record with on-demand pagination. The first record defines the columns; a new field appearing in a later record fails the flow, so use it for stable record structures.

Read more: HTTP API Connector and Work with Paginated APIs.

Working with the serialized nested columns

The nested fragments are ordinary string values, so you can:

• Load them as-is into JSON-aware destination columns — VARIANT (Snowflake), JSONB (PostgreSQL), SUPER (Redshift), JSON (BigQuery, MySQL, SQL Server as NVARCHAR) — and query them natively in the destination.
• Keep them as text in a CLOB/TEXT column or a file for archival and later processing.
• Parse them later in Etlworks when needed — see Convert a string in any format into a dataset.

Note: a nested element serialized into a string is a string — it is not navigable with dotted paths in local DataSet SQL. If the flow needs to join or navigate the nested structure in Etlworks, use the standard (non-streaming) nested read instead; see Working with Nested Documents and Formats.

Mapping and previews with very large sources

• Create Mapping and field discovery read only a sample of records (the Data structure sample records setting, default 5) instead of the whole file, so mapping large sources is fast. A field that first appears after the sample is not discovered — increase the sample size or add the mapping manually, and use Clear Cache in the mapping editor to refresh.
• Test Transformation stops reading once it has the preview rows, so previews of very large files return quickly. Content after the preview window is not validated by the preview.

Choosing the right technique

Availability

Full streaming read for JSON and XML, Stream JSON Response, and Stream CSV Response are available starting with Etlworks 10.0.0. All of them are opt-in and off by default. Flows executed by an Integration Agent require an Agent on 10.0.0 or later.