XML Adapters

Both the Data Management Console and Web Console provide a capability to Pivot a database column or field containing multiple values, such as a delimited list or a JSON document. This creates a new segment in the synonym that describes the internal structure of the field.

This option is now available for JSON documents. In previous releases, if the source synonym was for a JSON document itself, this option was not available.

The following HOLD syntax for creating a JSON HOLD file uses the BY fields in the report request to create a hierarchy of the JSON output.

ON TABLE HOLD NOKEYS AS app/filename FORMAT JSON STRUCTURE HIERARCHY

Example: Retaining the Query Structure Using HOLD FORMAT JSON

The following request retains the BY field hierarchy in the JSON HOLD file.

TABLE FILE WF_RETAIL_LITE
SUM COGS_US REVENUE_US
BY BUSINESS_REGION
BY PRODUCT_CATEGORY
BY PRODUCT_SUBCATEG
ON TABLE HOLD NOKEYS AS STRUCTJSON FORMAT JSON STRUCTURE HIERARCHY
END

The partial JSON output resulting from this request is shown in the following image.