Database source XML configuration reference

This is for:

Developer System Administrator

When creating a Database source in the Coveo Administration Console, you must provide an XML configuration defining the content to retrieve from your database.

This configuration consists of queries against your database to retrieve and copy data from record fields to Coveo fields. It should contain only read queries, or else you could make unwanted changes to your database.

This article documents all supported XML elements and their attributes. Use the table of contents to navigate quickly.

Dynamic values

Throughout this configuration, you can use dynamic values as placeholders that are replaced with actual database field values at indexing time.

Syntax: %[<DATABASE_FIELD_NAME>]

For example, in <FileName>%[CustomerID].txt</FileName>, the %[CustomerID] placeholder is replaced with actual customer IDs (JSmith00291, BAllen00815, etc.), while .txt is static. The resulting filenames are JSmith00291.txt, BAllen00815.txt, etc.

Dynamic values are supported in most element values throughout the configuration. Elements that don’t support dynamic values will note this explicitly.

Example

The following is a complete example of a Database source XML configuration. It queries a message table, uses a subquery to retrieve employee names from a separate table, defines common permissions via <CommonMapping>, and indexes each message with custom fields for author and employee details.

Complete example 
<?xml version="1.0" encoding="utf-8" ?>
<ODBC>
  <CommonMapping excludedItems="employeelist">
    <AllowedUsers>
      <AllowedUser type="Windows" allowed="true">
        <Name>everyone</Name>
        <Server></Server>
      </AllowedUser>
    </AllowedUsers>
  </CommonMapping>
  <Mapping type="message">
    <Accessor type="query">
      SELECT message.mid,
      message.sender,
      message.date,
      message.message_id,
      message.subject,
      message.body,
      message.folder
      FROM message
      WHERE DATE like '2001-04-07%'
    </Accessor>
    <AccessorSubQueries>
      <AccessorSubQuery name="FirstNameLastName" separator=";">
        SELECT firstName, lastName
        FROM employeelist
        WHERE Email_id = %[sender]
      </AccessorSubQuery>
    </AccessorSubQueries>
    <Fields>
      <Uri>https://www.coveo.com/Emails/details.aspx?Id=%[mid]</Uri>
      <ClickableUri>https://www.coveo.com</ClickableUri>
      <FileName>Message_%[mid].txt</FileName>
      <Title>Message_%[mid]</Title>
      <ModifiedDate>%[date]</ModifiedDate>
      <Body>%[body]</Body>
      <CustomFields>
        <CustomField name="msgAuthor">%[sender]</CustomField>
        <CustomField name="firstName">%[FirstNameLastName.firstName]</CustomField>
        <CustomField name="lastName">%[FirstNameLastName.lastName]</CustomField>
      </CustomFields>
    </Fields>
    <AllowedUsers>
      <AllowedUser type="CustomGroup" allowed="true">
        <Name>everyone</Name>
        <Server></Server>
      </AllowedUser>
    </AllowedUsers>
  </Mapping>
</ODBC>

Table of contents

Show table of contents

Root node (any name)

Root node

The outermost XML element wraps the entire configuration. Its name has no significance — the connector ignores it. You can use any valid XML element name (for example, <ODBC>, <DatabaseConfig>, or <MyConfig>).

All other elements documented below must be nested inside this root node.

Example:

<?xml version="1.0" encoding="utf-8" ?>
<ODBC>
  <!-- Configuration goes here -->
</ODBC>

Mapping (required)

Root node > <Mapping>

The <Mapping> element defines how to retrieve data from a table, how to index this data, and who should be allowed to access it through a Coveo-powered search interface.

You can define multiple <Mapping> elements to index content from different tables.

Attribute:

  • type (required):

    The name of the table to index.

Example:

<Mapping type="Customers">
  <Accessor type="object">Customers</Accessor>
  <Fields>
    <Uri>http://www.example.com/Customers/details.aspx?Id=%[ID]</Uri>
    <ClickableUri>http://www.example.com</ClickableUri>
    <ContentType>text/html</ContentType>
    <Title>%[Company] (%[ID])</Title>
    <Body>%[Company]
      %[FirstName] %[LastName]
      %[JobTitle]
      %[BusinessPhone]</Body>
    <CustomFields>
      <CustomField name="Type">Customer</CustomField>
      <CustomField name="ID">%[ID]</CustomField>
    </CustomFields>
  </Fields>
  <AllowedUsers>
    <AllowedUser type="Windows" allowed="true">
      <Name>everyone</Name>
      <Server></Server>
    </AllowedUser>
  </AllowedUsers>
</Mapping>

Accessor (required)

Root node > <Mapping> > <Accessor>

Defines the object or query to use to extract data from the table.

Attributes:

  • type (required):

    How to access the data. Allowed values: object, query.

    Use type="object" when you want to index an entire table or view without filtering, ordering, or joining. Otherwise, use type="query" and specify your SQL query as the <Accessor> element text content.

  • OrderByFieldName (optional, type="query" only):

    The name of the database column on which the query results are ordered. Required to enable pause/resume on update operations.

  • OrderByFieldType (optional, type="query" only):

    The .NET data type of the OrderByFieldName column (for example, DateTime, int, string). Usually auto-detected; specify only if your database doesn’t handle schema detection correctly.

  • IncrementalRefreshFieldName (optional, type="query" only):

    The name of the database column that stores the last modification date. Required to enable the refresh capability. Should match OrderByFieldName if both refresh and pause/resume are needed.

Example (object type):

<Accessor type="object">Customers</Accessor>

Example (query type):

<Accessor type="query">
  SELECT Shippers.Company AS ShipperName,
    Orders.[OrderID] AS ID,
    Orders.[CustomerID],
    Orders.[OrderDate],
    Orders.[ShippedDate],
    Customers.Company,
    Employees.[LastName],
    Employees.[FirstName],
    Products.[ProductName],
    Products.[ListPrice]
  FROM   Orders, [OrderDetails], Shippers, Customers, Employees, Products
  WHERE  Orders.[ShipperID] = Shippers.ID AND
    Orders.[CustomerID] = Customers.ID AND
    Orders.[EmployeeID] = Employees.ID AND
    [OrderDetails].[OrderID] = Orders.[OrderID] AND
    [OrderDetails].[ProductID] = Products.ID AND
    Orders.[OrderDate] >= [LAST_REFRESH_PARAMETER] 1
</Accessor>
1 Replace the [LAST_REFRESH_PARAMETER] placeholder with @LastRefresh in an SqlClient scenario or with ? otherwise.
Tip

To page through query results, you can add paging parameters to your accessor query. See Enabling pause/resume on updates.

AccessorSubQueries

Root node > <Mapping> > <AccessorSubQueries>

Contains one or more AccessorSubQuery child elements that define subqueries to complement the data retrieved by the main <Accessor> query. For each item returned by the main query, the subqueries are executed and their results are merged into the item.

Example:

<AccessorSubQueries>
  <AccessorSubQuery name="FirstNameLastName" separator=";">
    ...
  </AccessorSubQuery>
</AccessorSubQueries>
AccessorSubQuery

Root node > <Mapping> > <AccessorSubQueries> > <AccessorSubQuery>

Defines a single subquery to execute for each item returned by the main query. The subquery uses a master key in its WHERE clause to join with the main query results. The master key (for example, %[sender]) must match exactly the column name returned by the main accessor, including casing.

Attributes:

  • name (required):

    The subquery name, used to reference its results in the <Fields> section (for example, %[name.columnName]).

  • separator (required):

    The separator character used when concatenating multiple rows returned by the subquery.

  • allowDuplicates (optional):

    Whether duplicate rows are included in the concatenated results. Default: true. Set to false to ignore duplicates.

  • singleQuoteEscapeSequence (optional):

    The escape sequence for single quotes in returned field values. Default: '' (doubled single quote). Some databases require a different sequence (for example, \' for MySQL).

Example:

<AccessorSubQuery name="FirstNameLastName" separator=";" allowDuplicates="false">
  SELECT firstName, lastName
  FROM employeelist
  WHERE Email_id = %[sender]
</AccessorSubQuery>
Note

To reference subquery metadata in Fields, use the syntax %[subQueryName.fieldName] (for example, %[FirstNameLastName.firstName]).

See Retrieving complement information for details on using subqueries.

AccessorForItemsToDelete

Root node > <Mapping> > <AccessorForItemsToDelete>

Defines a query that returns items to delete from the index during refresh operations. Use this when items that were previously indexed should be removed based on a condition that changes over time.

The query must return at least the column used in the <Uri> dynamic value so that Coveo can identify which items to delete.

Item deletion takes place during refresh operations only. Ensure you have enabled the refresh capability on your source.

Attribute:

  • type (required):

    Must be query.

Example:

<AccessorForItemsToDelete type="query">
  SELECT message.mid
  FROM message
  WHERE message.date < DATEADD(month, -1, GETDATE())
    AND message.mid < 100
</AccessorForItemsToDelete>

Fields (required)

Root node > <Mapping> > <Fields>

Defines which database fields to associate with Coveo metadata for each indexed item. These associations are divided into two sections:

  • The immediate child elements (for example, <Uri>, <ClickableUri>, <Title>, <Body>) associate a database field directly with Coveo default fields. No source mapping and field creation are required for these elements.

  • The <CustomFields> child elements serve to specify database fields whose content you want to send to Coveo as custom metadata. You must subsequently create a source mapping and a field for these elements.

Example:

<Fields>
  <Uri>http://www.example.com/Customers/details.aspx?Id=%[ID]</Uri>
  <ClickableUri>http://www.example.com</ClickableUri>
  <ContentType>text/html</ContentType>
  <Title>%[Company] (%[ID])</Title>
  <Body>%[Company]
    %[First Name] %[Last Name]
    %[Job Title]
    %[Business Phone]</Body>
  <CustomFields>
    <CustomField name="Type">Customer</CustomField>
    <CustomField name="ID">%[ID]</CustomField>
  </CustomFields>
</Fields>
Uri (required)

Root node > <Mapping> > <Fields> > <Uri>

Populates the Coveo uri field, the item unique identifier. You must use a dynamic value to ensure uniqueness.

Also, if you don’t specify a value for <ClickableUri>, the <Uri> value is used as the URL to which an end user is directed when clicking the title of the search result.

Important

This value populates the unique identifier of the source item in the index. If the computed value of <Uri> changes, the item will be indexed as a new item during the next scheduled content update, effectively creating a duplicate in the index. For more information, see Item identifier and duplicates.

Coveo forbids using dynamic expressions that populate the Uri field with metadata extracted through a subquery. This is because Coveo wouldn’t be able to resolve the Uri value should the query ever fail. As a result, the impacted content items wouldn’t be indexed or might be deleted from your index.

Example:

<Uri>http://www.example.com/Customers/details.aspx?Id=%[ID]</Uri>
ClickableUri

Root node > <Mapping> > <Fields> > <ClickableUri>

Populates the Coveo clickableuri field, used as the URL of the page to open when a user clicks the search result.

Example:

<ClickableUri>http://www.example.com</ClickableUri>
PrintableUri

Root node > <Mapping> > <Fields> > <PrintableUri>

Populates the Coveo printableuri field, the URL to display to a user who is about to click the search result. If not provided, Coveo displays the <ClickableUri> value.

Example:

<PrintableUri>example.com</PrintableUri>
Title

Root node > <Mapping> > <Fields> > <Title>

Populates the Coveo title field, the item name to display on the search result.

Example:

<Title>Order ID: %[ID]: %[ProductName]</Title>
Body

Root node > <Mapping> > <Fields> > <Body>

Populates the non-binary body of the item which serves as the search result quickview.

Note

The <Body> and <BinaryBody> elements are mutually exclusive. Use <BinaryBody> instead if you need to index binary content (images, files).

Example:

<Body>
  Customer: %[Company]
  OrderDate: %[OrderDate]<br/>
  ShippedDate: %[ShippedDate]<br/>
  Shipped via: %[ShipperName]<br/>
  %[ProductName], $%[ListPrice]
</Body>
BinaryBody

Root node > <Mapping> > <Fields> > <BinaryBody>

Database BLOB field from which the binary body (for example, image, file) is retrieved.

Enter the database field name only (without %[] syntax).

Note

The <BinaryBody> and <Body> elements are mutually exclusive. Use <Body> instead if you need to index non-binary content.

Example:

<BinaryBody>customerLogo</BinaryBody>
FileName

Root node > <Mapping> > <Fields> > <FileName>

Populates the Coveo filename field.

Ensure you enter the complete file name, including its extension. Coveo uses the extension to determine which converter handles the item during indexing.

You must provide either a <FileName> or a <ContentType>.

Example:

<FileName>%[ID].txt</FileName>
ContentType

Root node > <Mapping> > <Fields> > <ContentType>

Type of content of an item (for example, text/html). Coveo uses this to determine which converter handles the item during indexing.

You must provide either a <FileName> or a <ContentType>.

Example:

<ContentType>text/html</ContentType>
ModifiedDate

Root node > <Mapping> > <Fields> > <ModifiedDate>

Populates the Coveo date field, the date on which the indexed item was last modified. Coveo requires date field values to support the refresh capability on your Database source.

Example:

<ModifiedDate>%[LastEditedOnDate]</ModifiedDate>
CustomFields

Root node > <Mapping> > <Fields> > <CustomFields>

Contains <CustomField> child elements that serve to specify database fields whose content you want to send to the Coveo Platform as custom metadata.

Example:

<CustomFields>
  <CustomField ...>...</CustomField>
  <CustomField ...>...</CustomField>
  ...
</CustomFields>
CustomField

Root node > <Mapping> > <Fields> > <CustomFields> > <CustomField>

Specifies a database field whose content you want to send to the Coveo Platform and the associated metadata name to use.

After an initial rebuild, you can create a source mapping and a field to index this metadata.

Attribute:

  • name (required):

    The metadata name to use.

Example:

<CustomField name="OrderDate">%[OrderDate]</CustomField>

AllowedUsers

Root node > <Mapping> > <AllowedUsers>

Defines the permission system that controls which users can access the indexed content through a Coveo-powered search interface.

Example:

<AllowedUsers>
  <AllowedUser type="Windows" allowed="true">
    <Name>domain\group</Name>
    <Server></Server>
  </AllowedUser>
</AllowedUsers>
AllowedUser

Root node > <Mapping> > <AllowedUsers> > <AllowedUser>

Defines the permission for a specific user or group. If access to your database is managed by Active Directory, use a type="Windows" attribute.

Attributes:

  • type (required):

    The type of user or group. Allowed values: Windows, CustomGroup, CustomUser.

  • allowed (required):

    Whether the specified user or group should be granted access (true or false).

Examples:

<AllowedUser type="CustomGroup" allowed="true">
<AllowedUser type="Windows" allowed="true">
  <Name>domain\group</Name>
  <Server></Server>
</AllowedUser>
Name

Root node > <Mapping> > <AllowedUsers> > <AllowedUser> > <Name>

The name of the user or group to which the permission applies.

You can define multiple users within a single AllowedUser element by using a semicolon (;) separated list in the <Name> element.

Examples:

<Name>%[FirstName] %[LastName]</Name>
<AllowedUser type="CustomUser" allowed="true">
  <Name>user1@domain.com;user2@domain.com;user3@domain.com</Name>
</AllowedUser>
Server

Root node > <Mapping> > <AllowedUsers> > <AllowedUser> > <Server>

The domain name of the user or group specified in <Name>.

Example:

<Server>mydomain.com</Server>

CommonMapping

Root node > <CommonMapping>

Specifies settings common to all or several <Mapping> elements in the configuration. Accepts the same child elements as <Mapping> (<Fields>, <AllowedUsers>).

Attribute:

  • excludedItems (optional):

    A comma-separated list of table names (matching the <Mapping> type attribute) to which the CommonMapping settings don’t apply.

Example:

<CommonMapping excludedItems="Customers">
  <Fields>
    <CustomFields>
      <CustomField name="ID">%[ID]</CustomField>
    </CustomFields>
  </Fields>
  <AllowedUsers>
    <AllowedUser type="Windows" allowed="true">
      <Name>everyone</Name>
      <Server></Server>
    </AllowedUser>
  </AllowedUsers>
</CommonMapping>