Generating Schemas

To gain more control over the schemas discovered dynamically through Automatic Schema Discovery, you can save the schema to a configuration file. The GenerateSchemaFiles property enables you to automatically generate schemas based on the data modeling settings configured in the connection string. The CreateSchema stored procedure enables you to create schemas for other XPaths after you connect.

Generating Schema Files Automatically

You can generate schemas when you connect or when you execute a query. The add-in will save the schemas to the folder specified by Location.

Set GenerateSchemaFiles to OnStart to generate schemas for all tables detected when you connect.
Set GenerateSchemaFiles to OnUse to generate a schema for the referenced table when you query the table.

Using the CreateSchema Stored Procedure

You can call the CreateSchema stored procedure to generate a schema for the XPaths you specify. Below are the stored procedure's inputs and outputs.

Input


Name	Type	Description
TableName	String	The name of the table and also the name of the schema (RSD) file.
URI	String	The Uniform Resource Identifier (URI) of the JSON resource.
JSONPath	String	The JSONPath of an element that repeats at the same height within the JSON document. (This is used to split the document into multiple rows). You can specify multiple paths in a semicolon-separated list.
FileLocation	String	The folder path where the generated schema (RSD) file will be stored. When specified the TableName will be used as the schema file name.
FileName	String	The complete schema (RSD) file name of the generated schema. This input takes precedence over FileLocation.

Output


Name	Type	Description
Result	String	Returns Success or Failure.

Example Stored Procedure Call

With DataModel set to FLATTENDOCUMENTS in the connection string, the example stored procedure call below results in a schema that flattens all the JSONPath arrays into a single table. See Flattened Documents Model for more information on this data model.

EXECUTE CreateSchema TableName='GenPeople', 
FileLocation='C:\\tests\\scripts', 
URI='C:\\tests\\people.json', 
JSONPath='$.people;$.people.vehicles;$.people.vehicles.maintenance'

Next Steps

Column Definitions: Change column names and data types or the XPath to the column value.
SELECT Execution: Access the HTTP request -- for example, to implement server-side search.
INSERT Execution: Enable inserts by mapping inputs from the INSERT statement to an HTTP request.
UPDATE Execution: Enable updates by mapping the updated column values to an HTTP request.
DELETE Execution: Enable deletes by mapping the primary key to an HTTP request.