You can customize the table schemas detected through Automatic Schema Discovery to change column names and data types, enable update functionality, and more. The processing operations of the CData SSIS Components for REST hide the complexity of processing data and communicating with remote data sources, but they also expose control over these layers through custom schemas.

Custom schemas are defined in configuration files. In this chapter we outline the structure of these files.

Generating Schema Files

Generating Schema Files allows you to persist and customize the dynamic schemas discovered through Automatic Schema Discovery.

Editing Schema Files

Tables and views are defined by authoring schema files in APIScript. APIScript is a simple configuration language that allows you to define the columns and the behavior of the table. It also has built-in operations that enable you to process REST. In addition to these data processing primitives, APIScript is a full-featured language with constructs for conditionals, looping, etc. However, as shown by the example schema, for most table definitions you will not need to use these features.

Example Schema

Below is a fully functional table schema that models the people document in the Raw Data example and contains all the components you will need to execute SQL to JSON/XML data sources.

The schema reflects the following connection string, which returns a single table containing the data delineated by the specified XPaths -- see Parsing Hierarchical Data for a guide to the different DataModel settings.

DataModel=FLATTENEDDOCUMENTS;URI=C:\people.xml;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance';Location=C:\myschemas;GenerateSchemaFiles=OnStart;Format=XML;

You can find more information on each of the components of a schema in Column Definitions, SELECT Execution, INSERT Execution, UPDATE Execution, and DELETE Execution. You can also create stored procedures to implement capabilities of your API that cannot be modeled as SELECT, INSERT, UPDATE, or DELETE statements. See Defining Stored Procedures for more information.

  <api:script>
     <api:info title="Persons" desc="Parse the OData Persons feed.">
    <!-- You can modify the name, type, and column size here. -->
    <!-- See Column Definitions to specify column behavior and use XPaths to extract column values from XML. -->
      <attr name="ID"           xs:type="int" key="true" readonly="false"   other:xPath="/feed/entry/content/properties/ID" />
      <attr name="EmployeeID"   xs:type="int"            readonly="true"    other:xPath="/feed/entry/content/properties/EmployeeID" />
      <attr name="Name"         xs:type="string"         readonly="false"   other:xPath="/feed/entry/content/properties/Name" />
      <attr name="TotalExpense" xs:type="double"         readonly="true"    other:xPath="/feed/entry/content/properties/TotalExpense" />
      <attr name="HireDate"     xs:type="datetime"       readonly="true"    other:xPath="/feed/entry/content/properties/HireDate" />
      <attr name="Salary"       xs:type="int"            readonly="true"    other:xPath="/feed/entry/content/properties/Salary" />
    </api:info>
    
    <api:set attr="uri" value="http://services.odata.org/V4/OData/(S(5cunewekdibfhpvoh21u2all))/OData.svc/Persons" /> 
  
    <!-- The XPath attribute of a schema is the path to a repeating element that defines the separation of rows -->
    <api:set attr="XPath" value="/feed/entry/" />
  
    <!-- See the xmlproviderGet page in the Operations subchapter to set any needed HTTP parameters. -->
    <api:set attr="ContentType"   value="application/atom+xml" />
  
  <!-- The GET method corresponds to SELECT. Here you can change the parameters of the request for data. The results of processing are pushed to the schema's output. See SELECT Execution for more information. -->
  <api:script method="GET">
    <api:call op="xmlproviderGet">
      <api:push/>
    </api:call>
  </api:script>
  
  <!-- To add support for INSERTS please see the INSERT Execution page within the help for further information and examples. -->
  <api:script method="POST">
    <api:set attr="method" value="POST"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Inserts are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for UPDATES please see the UPDATE Execution page within the help for further information and examples. -->
  <api:script method="MERGE">
    <api:set attr="method" value="PUT"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Updates are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  <!-- To add support for DELETES please see the DELETE Execution page within the help for further information and examples. -->
  <api:script method="DELETE">
    <api:set attr="method" value="DELETE"/>
    <api:call op="xmlproviderGet">
      <api:throw code="500" desc="Deletes are not currently supported."/>
      <api:push/>
    </api:call>
  </api:script>

  </api:script>