XML DB Connector

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.

<?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 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