The basic attributes of a column are the name of the column, the data type, whether the column is a primary key, and the XPath. The add-in uses the XPath to extract nodes from hierarchical data.

Mark up column attributes in the api:info block of the schema file. Set the XPath in the other:xPath property, as shown in the example below. You can also provide a description of each attribute using the desc property.

<api:info title="Persons" desc="Parse the OData Persons feed.">
    <attr name="ID"           xs:type="int" key="true" readonly="false"   other:xPath="content/properties/ID" desc="The ID associated with an individual person." />
    <attr name="EmployeeID"   xs:type="int"            readonly="true"    other:xPath="content/properties/EmployeeID" desc="The employee ID associated with the person." />
    <attr name="Name"         xs:type="string"         readonly="false"   other:xPath="content/properties/Name" desc="The name of the person." />
    <attr name="TotalExpense" xs:type="double"         readonly="true"    other:xPath="content/properties/TotalExpense" desc="The total experience associated with the person." />
    <attr name="HireDate"     xs:type="datetime"       readonly="true"    other:xPath="content/properties/HireDate" desc="The hire date of the person." />
    <attr name="Salary"       xs:type="int"            readonly="true"    other:xPath="content/properties/Salary" desc="The salary of the person." />
  </api:info>

The following sections provide more detail on using XPaths to extract columns and rows. To see the column definitions in a complete schema, refer to Customizing Schemas.

Defining Column XPaths

The other:xPath property is used to specify the XPath that selects the column's value.

Absolute paths start with a '/' and contain the full XPath to the nested data.

<attr name="ID"           xs:type="int" key="true" other:xPath="/feed/entry/content/properties/ID" desc="The ID associated with the person." /> 

Indexed XPath values can also be used to specify an element within the document in the case that multiple elements with the same name are nested at the same level. The indexes are zero based.

<api:info>
  <attr name=PhoneNumber1 xs:type="string" other:xPath="Person/PhoneNumber[0]" desc="The person's phone number." />
  <attr name=PhoneNumber2 xs:type="string" other:xPath="Person/PhoneNumber[1]" desc="The person's phone number." />
</api:info>

Notes:

• XPaths and column names (when used to generate the XPath) are case sensitive.
• Any paths specified outside an XPath will only be retrieved if they are found prior to a row (XPath) being found, with the exception of the last row pushed (which will push all found paths). This is due to each row being pushed in a streaming fashion.

Defining Row XPaths

A row XPath specifies the path to an element that repeats at the same height -- an object array that the add-in splits into rows. With DataModel set to FlattenedDocuments or Relational, you can specify more than one XPath in a semicolon-separated list. See Parsing Hierarchical Data for guides to these data modeling strategies.

The connection string can define the XPath property or you can define the row XPath as an attribute of an individual schema. Use the api:set keyword to define the row XPaths for a schema. With DataModel set to FlattenedDocuments or Relational, you can specify more than one XPath in a semicolon-separated list.

<api:set attr="XPath" value="/root/people;/root/people/vehicles;/root/people/vehicles/maintenance" /> 

A wildcard XPath can also be used and is helpful in the case that the XPaths are all at the same height but contain different names:

<api:set  attr="XPath" value="/feed/*" />

Defining Column Value Formats

You can set the other:valueFormat property to "aggregate" to identify a column as an aggregate.

• The aggregate option will return the aggregate found at the XPath specified. For example, consider the following:
  

      <repeat>
      <name>TestAggregate</name>
      <myobjcol1>
        <object1>myData</object1>
        <object1>myData1</object1>
        <object2>myData2</object2>
      </myobjcol1>
      </repeat>
      

  The following example extracts the myobjcol1 element to a column named MyObjCol1:
  

      <api:info>
        <attr name="MyObjCol1" xs:type="string" other:xPath="myobjcol1" other:valueFormat="aggregate" desc="An object column." />
      </api:info>
      <api:set attr="XPath" value="/repeat"/>
      

  This column value will be the following:
  

      <myobjcol1>
        <object1>myData</object1>
        <object1>myData1</object1>
        <object2>myData2</object2>
      </myobjcol1>
      

Mapping SELECT criteria to query parameters

Some APIs will support filtering the results of a request by specifying a query parameter. If this parameter maps to a WHERE clause, you can use the other:filter property to program this mapping.

• other:filter is a semicolon separated list of <parameter name>:<operator list>. <parameter name> is the name of the query parameter and <operator list> is a comma-separated list of operators used for the mapping. Valid operators are <, <=, =, >, >=, and LIKE.

The below example is for two query parameters, 'modifiedSince' and 'modifiedBefore', which filter the results based on the 'modifiedAt' column.

    <attr name="ModifiedAt"           xs:type="datetime" readonly="false"   other:xPath="content/properties/modifiedAt" other:filter="modifiedBefore:<;modifiedSince:>,>=,=" desc="Datetime when last modified." />
    

This example would take the query statement

SELECT * 
FROM <table> 
WHERE modifedAt < '<datetime>'

and generate a request that appends the query parameter '&modifiedBefore=<url encoded datetime>' to the url.