Making SQL Queries with MarkLogic

Table of contents

Reading Time: 3 minutes

MarkLogic is the only Enterprise NoSQL database, that brings all the features you need into one unified system. SQL is one of the many languages that has been supported by MarkLogic. So that data stored in a MarkLogic database can be easily consumed by technologies that require a SQL data source.

SQL queries can be sent via server-side applications code or through ODBC. The view is required so you need to create the view first, once the view has been created you are able to run the SQL queries against the view to retrieve the row data from the database. You can also run the SQL queries on the query console easily.

Create SQL and Schema databases

Create the database and the forest first and merge them respectively. During creating the database you need to set up the name of the schema as well as shown in the below picture:

The next step is scrolling down in the same create database part to enable the true index by setting it to true.

After this you just need to create the template view as shown in the picture:

After doing all the steps you just need to visit the query console and start working on the query part.

The benefit of using MarkLogic is there is no need to create tables . You insert individual structured documents (XML/JSON) where each document is as close as you would get to the idea of a table row in a relational database.

For use of SQL to query these documents, MarkLogic allows you to create views. These are similar to views in SQL and expose specific elements/attributes/properties of your documents.

SQL on MarkLogic in details

In the earlier version i.e. in MarkLogic 8, the range views are used to create the view. After that, the template view has been introduced to make these things easier and faster. Template view is introduced in MarkLogic 9 and onwards.

The content stored in a MarkLogic Server database is represented by the view and schema of the SQL clients. In General, SQL-92 standard of queries is supported by the MarkLogic in which the joins and aggregates are added.

Now the question arises what exactly is the SQL-92? So basically SQL-92 is the third revision of the SQL database query language. It is more efficient and has more features than the previous version of SQL.

You just need to select the new workspace and rename it according to you.

After that, you need to select the created database and after selecting the database you are able to run your queries.

Using the fn: doc function returns the document node of the resource found at the specified URI.

Everything is setup now , run the SQL query according to the need. Here is the example attached shown in the picture:

Conclusion

MarkLogic is the only Enterprise NoSQL database, that brings all the features you need into one unified system. By using MarkLogic we can simply and easily perform SQL queries.

Reference: