XML DB Connector
Setting up XML DB Connector
Create a Database Collection in SearchBlox
- After logging in to the Admin Console, select the Collections tab and click on Create a New Collection or "+" icon.
- Choose Database Collection as Collection Type.
- Enter a unique name for your collection (for example, XML).
- Choose Private/Public Collection Access and Collection Encryption as per the requirements.
- Choose the language of the content (if the language is other than English).
- Click Save to create the collection.
![XML2.jpg 1732](https://files.readme.io/bc84891-XML2.jpg)
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.
<?xml version="1.0" encoding="UTF-8" ?>
<root>
<rootAttr1>rootValue1</rootAttr1>
<people>
<personal>
<age>20</age>
<gender>M</gender>
<name>
<first>John</first>
<last>Doe</last>
</name>
</personal>
<jobs>support</jobs>
<jobs>coding</jobs>
<vehicles>
<type>car</type>
<model>Honda Civic</model>
<insurance>
<company>ABC Insurance</company>
<policy_num>12345</policy_num>
</insurance>
<features>sunroof</features>
<features>rims</features>
<maintenance>
<date>07-17-2017</date>
<desc>oil change</desc>
</maintenance>
<maintenance>
<date>01-03-2018</date>
<desc>new tires</desc>
</maintenance>
</vehicles>
<vehicles>
<type>truck</type>
<model>Dodge Ram</model>
<insurance>
<company>ABC Insurance</company>
<policy_num>12345</policy_num>
</insurance>
<features>lift kit</features>
<features>tow package</features>
<maintenance>
<date>08-27-2017</date>
<desc>new tires</desc>
</maintenance>
<maintenance>
<date>01-08-2018</date>
<desc>oil change</desc>
</maintenance>
</vehicles>
<addresses>
<type>work</type>
<zip>12345</zip>
</addresses>
<addresses>
<type>home</type>
<zip>12357</zip>
</addresses>
<source>internet</source>
</people>
<people>
<personal>
<age>24</age>
<gender>F</gender>
<name>
<first>Jane</first>
<last>Roberts</last>
</name>
</personal>
<jobs>sales</jobs>
<jobs>marketing</jobs>
<source>phone</source>
<vehicles>
<type>car</type>
<model>Toyota Camry</model>
<insurance>
<company>Car Insurance</company>
<policy_num>98765</policy_num>
</insurance>
<features>upgraded stereo</features>
<maintenance>
<date>05-11-2017</date>
<desc>tires rotated</desc>
</maintenance>
<maintenance>
<date>11-03-2017</date>
<desc>oil change</desc>
</maintenance>
</vehicles>
<vehicles>
<type>car</type>
<model>Honda Accord</model>
<insurance>
<company>Car Insurance</company>
<policy_num>98765</policy_num>
</insurance>
<features>custom paint</features>
<features>custom wheels</features>
<maintenance>
<date>10-07-2017</date>
<desc>new air filter</desc>
</maintenance>
<maintenance>
<date>01-13-2018</date>
<desc>new brakes</desc>
</maintenance>
</vehicles>
<addresses>
<type>home</type>
<zip>98765</zip>
</addresses>
<addresses>
<type>work</type>
<zip>98753</zip>
</addresses>
</people>
<rootAttr2>rootValue2</rootAttr2>
<rootAttr3>rootValue3</rootAttr3>
<rootAttr3>rootValue4</rootAttr3>
</root>
- The main object in this XML file is people
- The structure of objects in the file is represented in the following table.
Learn more on Parsing XML Data
![jsonrawdatastructure.png 569](https://files.readme.io/8161f30-jsonrawdatastructure.png)
JSON Objects in the file
Database Collection Settings
- The mandatory settings for Database collection for External DB Connectors are
Database Type
Database URL String
SQL Query - 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.
- Provide SQL Query, which fetches the data from XML file and saves it.
![XML1.jpg 1356](https://files.readme.io/24e4dbd-XML1.jpg)
- Save the settings.
- Check the settings by clicking Test Database Collection. The database connection has to be successful for the indexing to work.
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
- 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.
![XML 3.jpg 1423](https://files.readme.io/e047a8d-XML_3.jpg)
Schedule and Index
Sets the frequency and the start date/time for indexing a collection. Schedule Frequency supported in SearchBlox is as follows:
- Once
- Hourly
- Daily
- Every 48 Hours
- Every 96 Hours
- Weekly
- Monthly
The following operations can be performed in Database collections.
Action | Description |
---|---|
Schedule | For each collection, indexing can be scheduled based on the above options. |
To know more about Database Collection refer Database Collection
![Schedule 1.jpg 1573](https://files.readme.io/75d2b51-Schedule_1.jpg)
Search results in faceted search is shown here:
![jsonresultJSON.png 1062](https://files.readme.io/e4d8944-jsonresultJSON.png)
![xmlsearch.png 1847](https://files.readme.io/eacd70c-xmlsearch.png)
![XML 5.jpg 802](https://files.readme.io/0f892cf-XML_5.jpg)
Updated over 2 years ago