Running SQL on Marklogic Server

This blog will show how we can use SQL statements in the MarkLogic server. Let us start with creating a schema and required databases.

Create Schema Database and SQL database –

For the SQL on Marklogic Server creation follow the below process –

Every SQL database must have its own separate schema database.

1. Open the browser with the admin credentials

http://hostname : 8000

the hostname is your MarkLogic server machine hostname.

2. Click on the forest tree from the Left Menu icon.

3. Click on the Create tab on the top right it’s open the Forest page displays. Enter the SQLschemas as the forest name in the textbox. Click on the Ok.

1. Create the same forest SQLdata .

2. Click on the Databases from the Left menu icon and create the database. Enter the ‘SQLschemas’ as the name of the new database.

3. At the top of the page click Database->Forests

4. Check the SQLschemas box to attach the SQLschemas forest. Click Ok:

5. Click on the Create the Database from the left menu icon and Enter the ‘SQldata’ database as the name of the new database and select the schema database as the SQLschemas.

6. At the top of the page click Database->Forests. Click on the checkbox to attach the SQLdata to the SQLdata forest.

Create an ODBC App server –

The content stored in the Marklogic server database has the Schemas and Views.

Each content database used by a SQL client is managed by an ODBC App Server that accepts SQL queries from the SQL client and responds by returning MarkLogic Server data in tuple form. 

Only one database server can be managed through an ODBC App server.

For the Creation of the new ODBC App Server follow the below steps –

Open your admin interface and go through the below complete steps

1. Click on the Groups icon from the left menu.
2. Click on the group where you want to create the new server (like the Default etc).
3. After opening Click on the App Server from the left menu.
4. Click on the Create ODBC icon at the top of the page.
5. After the below page opening fills in the details of the ODBC server. Enter the odbc server name.
6. Enter the root directory for the module as /.
7. Enter the Server socket port number 5432. Enter the port number through which you want to make this ODBC server available.
8. The database contains application modules so it’s exact.

Load the Data into the database –

The following steps described how we can load data into the Marklogic database and how to use the Query console using the Marklogic. We can load the sample document into the database.

1. Enter the below URL to open the Query console.

http://hostname:8000/qconsole/

where the hostname is your Marklogic Server host.

2. We can add some sample data. Select the database as created earlier SQLdata. Add the following sample data.

3. Run the query.

Create Template Views

Create a template view in the main schema, named employees. Specify the Employee element as the context and columns for EmployeeID, FirstName, LastName, and Position.

Use tde:template-insert to insert the template document into the SQLschemas database as /employees.xml. Run the script SQLdata selected in the Database menu.

xquery version "1.0-ml"; 

import module namespace tde = "http://marklogic.com/xdmp/tde"
 
       at "/MarkLogic/tde.xqy";

let $employees :=

<template xmlns="http://marklogic.com/xdmp/tde"> 

  <context>/Employee</context> 

  <rows>

    <row>

      <schema-name>main</schema-name>

      <view-name>employees</view-name>

      <columns>

        <column>

          <name>EmployeeID</name>

          <scalar-type>int</scalar-type>

          <val>ID</val>

        </column>

        <column>

          <name>FirstName</name>

          <scalar-type>string</scalar-type>

          <val>FirstName</val>

        </column>

        <column>

          <name>LastName</name>

          <scalar-type>string</scalar-type>

          <val>LastName</val>

        </column>

        <column>

          <name>Position</name>

          <scalar-type>string</scalar-type>

          <val>Position</val>

        </column>

       </columns>

    </row>

  </rows>

</template>

return tde:template-insert("/employees.xml", $employees)

Similarly, create the other three Template Views for the “expenses” , “approvedvendor” , “expenselimit” Check the list of views created.

Enter the SQL Queries to test –

To test that whatever we have created is working correctly go through the below steps –

1. Go to the new query tab Select the appropriate Content source that we created.
2. Select the appropriate QueryType as SQL.
3. Execute the Query and see the result.

References :

https://docs.marklogic.com/