# Setting up XML DB Connector

## **Create a Database Collection in SearchBlox**

  • After logging in to the Admin Console, click Add Collection button. The Add Collection screen will now be displayed.

  • Enter a unique name for your collection (for example, XML).

  • Select the DB Collection icon.

  • Click Add to create the collection.

1013


## **XMLSample Data**

We will use the sample XML data given in the following for configuring SQL queries and database URL string. You can download the following data as a XML file, place it in a folder and follow the steps to test the DB connector.


  • The main object in this JSON file is **people**

  • The structure of objects in the file is represented in the following table. [Learn more on Parsing XML Data](🔗)

569


**JSON Objects in the file**

## **Configure SQL Query**

  • To fetch data from the XML, a select SQL query must be provided.

  • It is mandatory to map a unique field to uid

  • The query should follow SQL SELECT query format with mappings. [SQL Query for Database Collection](🔗) `SELECT <index field> as uid , <column name2> as title, <column name3> as content FROM <tablename> LIMIT 100` **Sample Query for XML sample data:** `SELECT [people].[personal.name.first] AS uid, [people].[personal.name.last] AS title,[people].[personal.age] AS age,[people].[personal.gender] AS gender,[people].[vehicles.type],[people].[vehicles.model],[people].[vehicles.insurance.company],[people].[vehicles.maintenance.date] FROM [people]`

  • In the preceding query you can see the fields are representated interms of hierarchy example: `[people].[personal.name.first]` `[people].[vehicles.maintenance.date] `

[Reference for select queries for Database XML Connector](🔗)

1342


If you have more than 100 entries in a table, specify the number of documents to be indexed by giving LIMIT value in the query. For example: `SELECT <index field> as uid , <column name2> as title, <column name3> as content FROM <tablename> LIMIT 100` Otherwise, a minimum of 50 rows will be indexed.

## ** Database Collection Settings**

  • Select database type as XML from the dropdown menu.

  • Enter the database URL string in the format as shown here: `jdbc:xml:DataModel=Relational;URI=C:\xmljson\person.xml;`

  • Provide the exact URI of your xml file.

  • In XMLPath provide the main object in the format `$.people'`

1299

  • Save the settings.

  • Check the settings by clicking Test Database Collection. The database connection has to be successful for the indexing to work.

705


## ** Index and Search**

  • Start indexing from the Index tab or dashboard.

1354


Search results in regular search and faceted search are shown as follows:

1062

1012

1073