Message_%[mid]

--- title: Database source XML configuration reference slug: l15b4451 canonical_url: https://docs.coveo.com/en/l15b4451/ collection: index-content source_format: adoc --- # Database source XML configuration reference When creating a Database source, you must provide an XML configuration defining the content to retrieve. This XML configuration should consist of queries against your database to retrieve and copy the data from record fields to Coveo fields. It should contain only read queries, or else you could make unwanted changes to your database. You can also [run subqueries](https://docs.coveo.com/en/l15b5020#complement-information-retrieval-using-subqueries) on every item to complete the information with more complex queries. See [Further Configuration](https://docs.coveo.com/en/l15b5020/) for other options you can implement. Your XML configuration should start with the following: ```xml ``` The nodes defined below should appear inside the `` node. The following is an example of a complete configuration: ```xml everyone SELECT message.mid, message.sender, message.date, message.message_id, message.subject, message.body, message.folder FROM message WHERE DATE like '2001-04-07%' SELECT firstName, lastName FROM employeelist WHERE Email_id = %[sender] https://www.coveo.com/Emails/details.aspx?Id=%[mid] https://www.coveo.com Message_%[mid].txt Message_%[mid] %[date] %[body] %[sender] %[FirstNameLastName.firstName] %[FirstNameLastName.lastName] everyone ``` ## Dynamic values Dynamic values are metadata values acting as placeholders for node values in your XML configuration. When writing your XML configuration, you associate Coveo fields and database fields with the following syntax: `%[]`. As a result, when indexing each of your database items, Coveo populates its field with the value of the specified database field. For example, in `%[CustomerID].txt`, the `%[CustomerID]` is dynamic and replaced with actual customer IDs, such as `JSmith00291` and `BAllen00815`. On the other hand, `.txt` is static. The resulting filenames are therefore `JSmith00291.txt`, `BAllen00815.txt`, etc., and Coveo indexes them as the value of its `filename` field. ## Mapping (required) The `Mapping` node defines how to retrieve data from the tables, how to index this data, and who should be allowed to access it through a Coveo-powered [search interface](https://docs.coveo.com/en/2741/). [Dynamic values](#dynamic-values) are supported. **Attribute:** . `type` (required): The name of the table to index. As an example, the following configuration indexes content from the `Customers` table. ```xml Customers http://www.example.com/Customers/details.aspx?Id=%[ID] http://www.example.com text/html %[Company] (%[ID]) %[Company] %[FirstName] %[LastName] %[JobTitle] %[BusinessPhone] Customer %[ID] everyone ``` ### Accessor (required) Provide a query string to use to extract the desired data from the table. Use the `type` attribute to specify whether the information stored in your database should be accessed using an object or a SQL query. Allowed values are `object` and `query` for SQL queries. The following example showcases the `object` type: ```xml Customers ``` This example showcases the `query` type: ```xml 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] >= [] ``` In this last example, you would replace `` with `@LastRefresh` in an SqlClient scenario or with `?` otherwise. #### Paging To specify how you want the content to be paged, add a paging expression to your accessor. This expression contains two parameters that you must replace with an integer that's greater than or equal to zero. The parameters are: * `@pageOffset`, which specifies the number of pages to skip before starting to return content. To start at page 1, enter `0`. * `@pageSize`, which specifies the number of items in a page. However, the expression to use varies depending on your [data provider type](https://docs.coveo.com/en/1885#connection-parameters-subtab): [cols="1,2"] |=== |Data provider type |Expression to use |Databricks |`LIMIT @pageSize OFFSET @pageOffset` |MySQL |`LIMIT @pageOffset, @pageSize` |Microsoft SQL Server |`OFFSET @pageOffset ROWS FETCH NEXT @pageSize ROWS ONLY;` |PostgreSQL |`LIMIT @pageSize OFFSET @pageOffset` |Oracle |`OFFSET @pageOffset ROWS FETCH NEXT @pageSize ROWS ONLY` |Redshift |`LIMIT @pageSize OFFSET @pageOffset` |=== For example, with a Microsoft SQL Server Database source, the following accessor starts paging at the first page and returns 100 items per page: ```xml Select id, title, dateModified, content, author FROM blog WHERE dateModified>=[PARAMETER] order by dateModified OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY; ``` ### AllowedUsers If you don't want your database content to be available to anyone who can access your Coveo-powered search interface, define a permission system for Coveo to use. Coveo will replicate this permission system in your search interface, displaying only the results a user is allowed to access. If access to your database is managed by Active Directory, include an `AllowedUsers` node with a `type="Windows"` attribute. For example: ```xml domain\group ``` #### AllowedUser Define the permission to give to the user or group specified under [Name](#name). **Attributes:** . `type` (required): The type of user/group (`Windows`, `CustomGroup`, or `CustomUser`). . `allowed` (required): Whether the user or group specified under [`Name`](#name) should be granted access to the content retrieved with the parent[`Mapping`](#mapping-required) configuration. ```xml ``` You can define multiple allowed users within a single `AllowedUser` node by separating them by a semicolon (`;`). ##### Name Provide the name of the user or group to which the permission defined under [AllowedUser](#alloweduser) applies. [Dynamic values](#dynamic-values) are supported. ```xml %[FirstName] %[LastName] ``` ##### Server Provide the domain name of the user or group specified under [Name](#name). ### Fields List the database fields to map with Coveo [default field](https://docs.coveo.com/en/1833#field-origin) when indexing an item. The child nodes should be named after Coveo fields. [Dynamic values](#dynamic-values) are supported. See also [CustomFields](#customfields). The following example shows how data from the Customers table will be indexed in the Coveo `title` and `body` fields. ```xml http://www.example.com/Customers/details.aspx?Id=%[ID] http://www.example.com text/html %[Company] (%[ID]) %[Company] %[First Name] %[Last Name] %[Job Title] %[Business Phone] Customer %[ID] ``` #### BinaryBody Database BLOB field from which Coveo should retrieve the binary body (for example, image, file) of the item to index. [Dynamic values](#dynamic-values) are supported, however you must enter the name of the desired field only, that is, without the `%[]`. ```xml customerLogo ``` > **Note** > > Alternatively, you can use a `Body` node to index non-binary content as the body of an item. > The `BinaryBody` and [`Body`](#body) nodes are mutually exclusive. #### Body Body of the item to index. [Dynamic values](#dynamic-values) are supported. **Example:** ```xml Customer: %[Company] OrderDate: %[OrderDate]
ShippedDate: %[ShippedDate]
Shipped via: %[ShipperName]
%[ProductName], $%[ListPrice] ``` > **Note** > > Alternatively, you can use a `BinaryBody` node to index binary content as the body of an item. > The `Body` and [`BinaryBody`](#binarybody) nodes are mutually exclusive. #### ClickableUri URI of the page to open when a user clicks a Database search result in a Coveo-powered search interface. It can be identical to the [Uri](#uri-required). [Dynamic values](#dynamic-values) are supported. See also [`PrintableUri`](#printableuri). #### ContentType Type of content of an item. Enter `text/html` or the extension of item. Coveo uses this information to determine which [converter](https://docs.coveo.com/en/2735/) should handle the item during the [indexing process](https://docs.coveo.com/en/1893/). If your database records have filenames, you can provide a [`FileName`](#filename) instead. It is required to provide either a `FileName` or a `ContentType`. #### CustomFields Associate database fields with Coveo [custom fields](https://docs.coveo.com/en/1833#field-origin). ##### CustomField Each `CustomField` node represents a Coveo [custom field](https://docs.coveo.com/en/1833#field-origin) and the data it contains. [Dynamic values](#dynamic-values) are supported. For instance, with the following mapping, the content of the `Order Date` database field will populate the Coveo `OrderDate` field. ```xml %[OrderDate] ``` **Attribute:** . `name` (required): The name of the Coveo custom field to populate with the database field data. #### FileName Name of the item to index. [Dynamic values](#dynamic-values) are supported. Include the extension. Coveo uses the extension to determine which [converter](https://docs.coveo.com/en/2735/) should handle the item during the [indexing process](https://docs.coveo.com/en/1893/). For instance, with the following configuration, Coveo will index each item as a TXT file named after the item ID. ```xml %[ID].txt ``` If your database records do not have filenames and you do not want to create an artificial one, provide a [`ContentType`](#contenttype) instead. It is required to provide either a `FileName` or a `ContentType`. #### ModifiedDate Date on which the indexed item was last modified. [Dynamic values](#dynamic-values) are supported. This date is used by Coveo to [enable the refresh capability](https://docs.coveo.com/en/l15b5020#enabling-the-refresh-capability) on your Database source. ```xml %[LastEditedOnDate] ``` #### PrintableUri URL to display to a user who is about to click a Database search result in a Coveo-powered search interface. For instance, when clicking an item, users could be [redirected](#uri-required) to `example.com/id/123456`. However, you could decide to overwrite the displayed URL with the following node: ```xml example.com ``` [Dynamic values](#dynamic-values) are supported. If you do not provide a `printableUri`, Coveo displays the [`clickableUri`](#clickableuri). #### Title Item name to display on a Coveo-powered search interface. Typically, it corresponds to the title of the indexed item. [Dynamic values](#dynamic-values) are supported. In the following example, `%[ID]` and `%[Product Name]` are used to give each entry its own name based on the `ID` and `product name` fields in the `Orders` table. ```xml Order ID: %[ID]: %[ProductName] ``` #### Uri (required) Address to which an end user is directed when clicking the title of a Database search result in a Coveo-powered search interface. [Dynamic values](#dynamic-values) are supported, as shown in the following example. ```xml http://www.example.com/Customers/details.aspx?Id=%[ID] ``` > **Important** > > This value populates the unique identifier of the source item in the index. > If the computed value of `` 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](https://docs.coveo.com/en/pb5g3451/). > **Note** > > Even if your database records aren't available through a URI, you must still provide a value that will be different for all items, since the Coveo index also uses the `uri` value as a unique identifier for items. > In this case, you can use a dynamic value whose value is unique to each item. > > However, Coveo forbids using dynamic expressions that populate the `Uri` field with metadata extracted through a [subquery](https://docs.coveo.com/en/l15b5020#complement-information-retrieval-using-subqueries). See also [`ClickableUri`](#clickableuri) and [`PrintableUri`](#printableuri). ## CommonMapping The `CommonMapping` node allows you to specify settings common to all or several of the [mappings](#mapping-required) provided in the configuration file. Use the `excludedItems` node attribute to list the objects to which the `CommonMapping` settings don't apply. For instance, the following configuration excludes the `Customers` table. ```xml %[ID] everyone ```