JSON XML CSV Query Browser

Within the Connector Application is a tool called the JSON/XML/CSV Query Browser that allows data to be queried and retrieved from web services or files that store data in either JSON, XML or CSV text data formats. This query browser can help you find and understand the kinds of data that are stored within JSON, XML or CSV documents, test querying data from JSON, XML and CSV documents, test connection credentials to web services that return JSON/XML/CSV data over the web, test the validity of JSON, XML or CSV data, as well as copy data from JSON/XML/CSV data stores. The XML/JSON/CSV Query Browser can be a great tool to help data specialists work with JSON, XML or CSV data, as well as help with setting up a Generic adaptor within the Connector.

Topics

Prerequisites

Please make sure you have read through the Get Started/Overview before continuing down this document.
Additionally make sure you are familiar with the following:

JavaScript Object Notation (JSON)
Extensible Markup Language (XML)
Comma Separated Values (CSV)
Hyper Text Transport Protocol (HTTP)

Overview

Many different types of software support storing data in both the JSON, XML and/or CSV data formats. Both JSON and XML formats store data in hierarchical data structures (similar to how file systems store files (data) within folders, that are stored within other folders). Using the JSON/XML/CSV query browser it can find and read through the JSON or XML data, convert the data into tabular data and show the data as records in rows and columns (instead of trees). Additionally the query browser supports manipulating the JSON/XML data before it is displayed, allowing database-like querying to alter the contents being retrieved.

For CSV text data that is already stored in a tabular structure, the query browser will read the first row of the CSV data as a header row, where it can obtain the names of all of the columns in the data. It will then use this list to create properties for every other row, with each row being converted into a JSON object, with the row's values stored as property values. Each of these JSON objects will be added to an overall array called dataRecords.

Retrieving the JSON, XML or CSV Data

Before the query browser can query JSON, XML or CSV data it first must find where the data is located, this can be from one of the following locations:

From a plain text file that stores JSON data on a computer
From a plain text file that stores XML data on a computer
From a plain text file that stores CSV data on a computer
From a web service that can be called using the HTTP protocol to retrieve JSON from an API (application programming interface).
From a web service that can be called using the HTTP protocol to retrieve XML from an API (application programming interface).
From a web service that can be called using the HTTP protocol to retrieve CSV from an API (application programming interface).
From JSON data that has been placed directly within the query browser's Raw Data tab.
From XML data that has been placed directly within the query browser's Raw Data tab.
From CSV data that has been placed directly within the query browser's Raw Data tab.
From JSON data that has been placed directly within the query browser's HTTP Request Body tab.
From XML data that has been placed directly within the query browser's HTTP Request Body tab.
From CSV data that has been placed directly within the query browser's HTTP Request Body tab.

Reading The JSON, XML or CSV Data

Once the JSON, XML or CSV data has been retrieved the query browser will then try to read through it and find an array of objects that can be treated as a record set. If XML data is being read the query browser will first convert the XML data into JSON. If CSV data is being read the query browser will first convert the CSV data into JSON. This then allows the query browser to use the JSONPath syntax to read through the hierarchical tree to find objects that will be placed into a record set. Once a record set has been generated then the query browser will read through each object in the record set and find the objects related to it that can be placed into each specified field. Within the query browser's Data Fields table this defines the fields that you wish to find data with for each record in the data set. The field's themselves can store JSONPath syntax as well as use JSON Field functions to locate the objects and attributes that store data within the JSON, XML or CSV data. Once data for each of the defined fields has been retrieved then the resulting data can be shown within the query browser's results table.

Query Browser Settings

Setting	Description
Read Document From	Set how the JSON, XML or CSV data will be queried from.
File Path	Set the path to the file on the computer that stores either the JSON, XML or CSV data. The path may contain UNC names to allow data to be retrieved from other computers over the network if allowed. This setting is only required if the Read Document From option is set to get data from a file.
URL Path	Set the URL used to call to retrieve JSON, XML or CSV data from a HTTP web service. This setting is only required if the Read Document From option is set to get data from a HTTP
HTTP Request Headers	Set the headers that will be added to the HTTP request used to get the JSON, XML or CSV data. Place each header record in a row within the text area. This setting is only required if the Read Document From option is set to get data from a HTTP web service.
HTTP Request Method	Sets the method used to make HTTP requests to a web service for obtaining JSON or XML data. If the Method is set to POST, PUT or DELETE then any data placed within the HTTP Request Body tab will be placed into the body of the HTTP request. This setting is only required if the Read Document From option is set to get data from a HTTP web service.
HTTP Timeout	Set the amount of seconds that the query browser will give up if no response has been received when calling a web service to retrieve JSON, XML or CSV data. This setting is only required if the Read Document From option is set to get data from a HTTP web service.
JSON RecordSet Path	Path that defines how to find objects within the JSON, CSV or XML data that will be placed into the record set, and used as records. Set JSONPath syntax to set the path. Multiple paths can be set by placing the --UNION-- text between each path. When this occurs each path between the --UNION-- delimiter will be called sequentially to read through the JSON/XML/CSV data, then its rows will be appended to previous records obtained. This works similar to the UNION clause in the SQL database language.
Data Fields	Add one or more rows to the table, with each row specifying one data field that will be queried for a data set record. In the Data Field column set either JSONPath syntax, literal text enclosed in single or double quote characters, or a JSON Field function. If setting JSONPath syntax the path only needs to start from the record object that was located within the record set.
Raw Data	Set the JSON, XML or CSV data that will be directly queried. This setting is only required if the Read Document From option is set to get data from the Raw Data tab. Also note that data within this tab will be replaced with JSON, XML or CSV data queried from HTTP webservices or files if the Read Document From option is set to do so.
HTTP Request Body	Set the content that will be placed within the body of the HTTP request if the Read Document From setting is set to Read JSON Document From HTTP Request, or Read XML or CSV Document From HTTP Request, and the HTTP Request Method is set to either POST, PUT, or DELETE. The content placed within this body could be JSON data, XML data, SOAP data or any CSV text data.

Document last modified on the 13th December 2018