Transform the Data
Representational State Transfer (REST) data sources retrieve data from a web service in either JSON or XML format. If the data is in JSON format and can be extracted into tabular format, the JSON data can be converted to XML format. Then you must use an eXtensible Stylesheet Language (XSL) transformer in order to convert it to a tabular format in the CIS.
Complete these steps in order to transform JSON-formatted data from the REST data sources into tabular format:
- Upgrade Studio to the Version 6.2.3.00.22 patch, at a minimum. This provides a new functionality, Design by Example, which makes it easier to obtain the schema for the XSL Transformation (XSLT) procedure so that you can map the JSON output to tabular output.
- When you create the REST data source, check the JSON Format check box:
- If you already created the REST data source, locate the Header and Body Parameters definitions from the panel, remove all of the current parameter definitions, and save.
- Click Design by Example. If already created, go to the operation view.
- Choose the top-level element of your JSON structure, which is likely to be the last line in the list, and click OK.
- Give the parameter a name and click Save.
This creates an XML Schema Definition Set that you can view in your data source resources.
This also transforms the JSON output to XML so that the XSL transformer can process it. Open the operation and view the output structure, which shows you the structure that is used in the XSLT procedure.
- Execute the operation in order to transform the JSON output into XML format.
- Create a new XSLT (New > Transformation > XSLT Transformation) and bind it to your REST data source operation.
- Choose the columns that you want in your tabular data and run the XSLT.