api:info

The api:info keyword is used to describe the metadata for scripts. This information is used by the add-in to implement basic error checking on user input and to set default values. The api:info keyword can contain the following:

Column definitions
Input parameters the script expects
Output the script produces

Table Parameters

The following parameters can be defined for the table itself:

desc[ription]: The description for the table. If a description is not provided, the name of the script is used.
title: The title of the script. If a title is not provided, the name of the script is used.
methods: The HTTP methods that can be executed against this script. SELECT, INSERT, UPDATE, and DELETE correspond to HTTP GET, POST, PUT/PATCH/MERGE, and DELETE, respectively.
url: The URL of the script author.
keywords: The keywords that describe the script.

Input, Output, and Column Parameters

The api:info keyword has additional parameters contained within its scope that define columns as well as script inputs and outputs. Note these parameters are not API Script keywords, but additional information for api:info.

attr

The attr parameter describes a table column and includes at least two attributes: name and data type. The name must be an alphanumeric string.

Other optional attributes provide more information about the column and enable the add-in to represent these columns correctly in various tools.

<attr 
  name="Name"
  xs:type="string"
  other:xPath="name/full"
  readonly="true"    
  columnsize="100"
  desc="The name of the person." 
/>

name: The alphanumeric string that defines the name of the column.
xs:type: The data type of the column. The string, int, double, datetime, and boolean types are supported.
other: Attributes prefixed with 'other:' that provide extra information. These other properties can be operation specific. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.
desc[ription]: A short description of the column.
key: Whether the column is part of the primary key in the table.
readonly: Whether the column can be updated. Allowed values are true and false.
req[uired]: Whether the column must be specified in an insert. Allowed values are true and false.
def[ault]: The default value for the column if none is specified.
values: A comma-separated list of the valid values for the column. If specified, the engine will throw an error if the specified column value does not match one of the allowed values.
reference[s]: The foreign key to the primary key of another table. The foreign key is specified with the following syntax: table.key. For example: "Employees.EmployeeId".
columnsize: The maximum character length of a string or the precision of a numeric column. The precision of a numeric column is the number of digits.
scale | decimaldigits: The scale of a decimal column. The scale is the number of digits to the right of the decimal point.
isnullable: Indicates whether the column accepts null values. Note that this does not prevent sending or receiving a null value.

input

Input parameters are used to describe inputs to the script.

Input elements can be used in schemas for both tables and stored procedures.

The attributes of this parameter provide users with more information about the input and allow the engine to perform rudimentary checks.

In stored procedure schemas, one or more input elements are used to describe the inputs of the stored procedure. In table schemas an input element defines a pseudo column, a column that can only be used in the WHERE clause.

For XML data sources, pseudo columns cannot contain an XPath.

<input
  name="name"
  desc="Example of an input parameter"
  default="defValue"
  req="true"
  values="value1, value2, value3"
  xs:type="string" 
/>

name: The name of the input. An alphanumeric string that may additionally contain the following: "#" denotes that the input can have multiple values, "myprefix:*" denotes a set of inputs with the same prefix, and a value of "*" denotes arbitrary input parameters.
desc[ription]: A short description of the input.
xs:type: The data type of the input. The string, int, double, datetime, and boolean types are supported.
def[ault]: The default value to be used when no input value is supplied in the script call.
key: Whether the input is a primary key.
req[uired]: Whether the input is required. The engine will throw an error if the required input is not supplied and there is no default value defined. Allowed values are true and false.
values: A comma-separated list of the allowed values for the input. If specified, the engine will throw an error if the specified input does not match one of the allowed values.
alias: The alias of the input.
other: Attributes prefixed with "other:" that provide extra information. These other properties can be operation specific.

output

Output parameters describe the output of the operation. However, they are ignored by the add-in. Thus, the output of the operation can be entirely independent of what is defined in the info block.

<output  name="Id" 
         desc="The unique identifier of the record."
         xs:type="string"
         other:xPath="content/properties/Id" 
/>

name: The name of the output. "myprefix:*" denotes a set of outputs with the same prefix, and a value of "*" denotes arbitrary output parameters.
xs:type: The data type of the output. The string, int, double, datetime, and boolean types are supported.
desc[ription]: A short description of the output.
columnsize: The maximum character length of a string or the precision of a numeric output. The precision is the number of digits.
other: Attributes prefixed with 'other:' that provide extra information about the output. For example, other:xPath specifies the XPath to a node in a local or remote resource. The XPath can be relative to a RepeatElement.

Examples

Describes the metadata for a typical table with columns of different data types:

<api:info title="NorthwindOData" desc="Parse an XML document (NorthwindOdata.xml) into rows.">
  <attr name="ID"           xs:type="int" key="true" other:xPath="content/properties/ID" />
  <attr name="EmployeeID"   xs:type="int"            other:xPath="content/properties/EmployeeID" />
  <attr name="Name"         xs:type="string"         other:xPath="content/properties/Name" />
  <attr name="TotalExpense" xs:type="double"         other:xPath="content/properties/TotalExpense" />
  <attr name="HireDate"     xs:type="datetime"       other:xPath="content/properties/HireDate" />
  <attr name="Salary"       xs:type="int"            other:xPath="content/properties/Salary" />
</api:info>

The following describes the metadata for a stored procedure that searches the Web using the Bing Search API:

<api:info title="SearchWeb"    desc="Use Bing to search the Web."> 
  <output name="Id"          xs:type="string"    other:xPath="content/properties/ID"          />
  <output name="URL"         xs:type="string"    other:xPath="content/properties/Url"         />
  <output name="Title"       xs:type="string"    other:xPath="content/properties/Title"       />
  <output name="Description" xs:type="string"    other:xPath="content/properties/Description" />
  <output name="DisplayURL"  xs:type="datetime"  other:xPath="content/properties/DisplayUrl"  />

  <input name="SearchTerms"    />
</api:info>

Excel Add-In for REST