Loading TOC...
SQL Data Modeling Guide (PDF)

SQL Data Modeling Guide — Chapter 3

Creating Template Views

MarkLogic allows you to define a template view that specifies which parts of the document make up a row in a view, and then query that view from a server-side program with xdmp:sql, mlsql, or ODBC. You can also query that view server-side from the MarkLogic Optic API, which is a fluent JavaScript and XQuery interface with the ability to perform joins and aggregates on views over documents. Template views are a simple, powerful way to specify a relational lens over documents, making parts of your document data accessible via SQL. The Optic API gives developers idiomatic JavaScript and XQuery access to relational operations over rows, combined with rich document search. The Optic API is described in the Optic API for Relational Operations chapter in the Application Developer's Guide.

This chapter describes how to configure MarkLogic Server and create template views to model your MarkLogic data for access by SQL. Template views can also be created using the TDE API described in MarkLogic XQuery and XSLT Function Reference.

The focus of this chapter is on the template elements that are specific to creating views. The Template Driven Extraction (TDE) chapter in the Application Developer's Guide describes the template elements that are common to all types of data-extraction templates.

This chapter contains the following topics:

Template View Elements

A template view contains the following elements and their child elements:

ElementDescription
description
Optional description of the template.
collections
  collection
  collections-and
    collection
Optional collection scopes. Multiple collection scopes can be ORed or ANDed. For details, see Collections in the Application Developer's Guide.
directories
  directory
Optional directory scopes. Multiple directory scopes are ORed together. For details, see Directories in the Application Developer's Guide.
vars
  var
Optional intermediate variables extracted at the current context level. For details, see Variables in the Application Developer's Guide.
rows
  row
   schema-name
   view-name
   view-layout
     sparse
     identical
   columns
     column
       name
       scalar-type
       val
       nullable
       default
       invalid-values
          ignore
          reject
       reindexing
          hidden
          visible
       collation

rows is a sequence of row descriptions and mappings, as described in Row.

columns is sequence of column descriptions and mappings, as described in Columns.

scalar-type is the type for the val. See Type Casting in the Application Developer's Guide for details.

templates
  template
Optional sequence of sub-templates. Creating Views from Multiple Templates and Creating Views from Nested Templates.

You cannot use tde:template-insert to insert multiple templates created by means of the <templates> element. You must use xdmp:document-insert instead.

path-namespaces
  path-namespace
Optional sequence of namespace bindings. For details, see path-namespaces in the Application Developer's Guide..
context
The lookup node that is used for template activation and data extraction. For details, see Context in the Application Developer's Guide.
enabled
A boolean that specifies whether the template is enabled (true) or disabled (false). Default value is true.

The context, vars, and columns identify XQuery elements or JSON properties by means of path expressions. Path expressions are based on XPath, which is described in XPath Quick Reference in the XQuery and XSLT Reference Guide and Traversing JSON Documents Using XPath in the Application Developer's Guide.

Row

A row definition contains:

  • A unique schema-name to which the view belongs.

    This schema cannot contain Range Views.

  • A unique view-name specifies the target view. Extracted rows under a row section are added to its target view. Multiple templates can reference the same target view.
  • A view-layout element:
    • If its value is set to identical (default), the view declaration must be consistent between templates; same column names, column data types, and column nullability.
    • A value of sparse is useful when you use more than one template to define a view. For example, you may want a view that has multiple contexts and an optional column that matches some, but not all, of the documents in the database.target view declaration can have other nullable columns not listed under the current row. For example, if a view is referenced in template T1 using columns (A,B,C) and in template T2 using columns (A,B,D), the resulting view will have all 4 columns (A,B,C,D). Column A and B are present in both T1 and T2 and can be declared as non nullable. However, columns C and D must be nullable. For an example, see Creating Views from Multiple Templates.
  • A sequence of column descriptions each specifying a column name, data type (scalar-type) and data mapping (val). See Columns. The scalar-type is the type for the val. See Type Casting in the Application Developer's Guide.

For example:

    <row>
      <schema-name>main</schema-name>
      <view-name>expenses</view-name>
      <columns>
        <column>
          <name>EmployeeID</name>
          <scalar-type>int</scalar-type>
          <val>EmployeeID</val>
        </column>
        <column>
          <name>Date</name>
          <scalar-type>date</scalar-type>
          <val>Date</val>
        </column>
        <column>
          <name>Amount</name>
          <scalar-type>decimal</scalar-type>
          <val>Amount</val>
        </column>
       </columns>
    </row>

Columns

A column definition contains:

  • The column name. A column is uniquely identifiable by its schema, view, and column names.
  • The last data projection into the column described inside the val element. The simplest form of projection is a child node under the current context, like EmployeeID in the example above <val>EmployeeID</val>. See Template Dialect and Data Transformation Functions in the Application Developer's Guide for the types of expressions allowed in a val.
  • The column's SQL datatype scalar-type. The result of the val expression is automatically casted to the specified scalar type. Users do not have to explicitly create the result in the target datatype. See Type Casting in the Application Developer's Guide.
  • By default, a column is not nullable. However, you can allow a column to have no values by adding <nullable>true</nullable> to the corresponding column element. You can specify a default value for a column by adding <default>value</default>. A null value will be replaced by the default value.
  • An invalid-values element that controls the behavior when cell values cannot be coerced to their datatype:
    • If invalid-values is set to reject (default). The server should error out and indexing should stop.
    • If invalid-values is set to ignore, the entire row is skipped if any non-nullable column has a non-castable value. For nullable columns, a cell with a non-castable value is set to null.

The following table describes the results from the possible combinations of ignore and reject on nullable and non-nullable (<nullable>false</nullable>) columns. The Default Value column specifies whether or not a default value is specified for the column. The Invalid Input column describes what happens when the cell value cannot be coerced to the specified datatype. The Missing Input column describes what happens when there is no value available to populate the column.

Invalid ValuesnullabilityDefault ValueInvalid InputMissing Input
ignorenullableno defaultskip cellskip cell
default valuedefault valuedefault
non-nullableno defaultskip rowskip row
default valuedefault valuedefault value
rejectnullableno defaultrejectedskip cell
default valuerejecteddefault value
non-nullableno defaultrejectedrejected
default valuerejectedrejected

For example:

<column>
  <name>EmployeeID</name>
  <scalar-type>int</scalar-type>
  <val>EmployeeID</val>
</column>

<column>
  <name>EmployeeID</name>
  <scalar-type>int</scalar-type>
  <val>EmployeeID</val>
  <nullable>true</nullable>
  <invalid-values>ignore</invalid-values>
</column>

<column>
  <name>Brand</name>
  <scalar-type>string</scalar-type>
  <val>Brand</val>
  <default>generic</default>
  <invalid-values>reject</invalid-values>
</column>

<column>
  <name>SSN</name>
  <scalar-type>string</scalar-type>
  <val>id[@root='2.16.840.1.113883.4.1']/@extension</val>
</column>

<column>
  <name>Name</name>
  <scalar-type>string</scalar-type>
  <val>concat(patient/name/given[1],' ',patient/name/family)</val>
</column>

Defining View Scope

The scope of the view is used to constrain the view to the documents in particular collections or directories. The scope is optional, so do not specify a scope if you elect not to set the scope of the view.

For details on defining a template view scope, see Collections and Directories in the Template Driven Extraction (TDE) chapter in the Application Developer's Guide

Example Documents

The template views described below are written to extract data from documents, like the XML medical document shown below

In XQuery, insert the following document:

let $med :=
<Citation Status="Completed">
  <ID>69152893</ID>
  <PMID>5717905</PMID>
  <Article>
    <Journal>
      <ISSN>0043-5341</ISSN>
      <JournalIssue>
        <Volume>118</Volume>
        <Issue>49</Issue>
        <PubDate>
          <Year>1968</Year>
          <Month>12</Month>
          <Day>7</Day>
        </PubDate>
      </JournalIssue>
    </Journal>
    <ArticleTitle>
       The Influence of Calcium on Cholesterol in Human Serum
    </ArticleTitle>
    <AuthorList>
      <Author>
        <LastName>Doe</LastName>
        <ForeName>John</ForeName>
      </Author>
      <Author>
        <LastName>Smith</LastName>
        <ForeName>Jane</ForeName>
      </Author>
    </AuthorList>
  </Article>
</Citation>

return xdmp:document-insert("med1.xml", $med) 

In JavaScript, insert the following document:

declareUpdate();

xdmp.documentInsert(
   "med2.json",
    { "Journal": {
    "Issue": 103,
    "Title": "Bone Density Studies",
    "Date": "8/1/2009",
    "Author": "John Simson" }})

Example View Templates

This section shows two templates, one in XML and one in JSON, that define a view on the document in Example Documents. The view templates are:

XML View Template

The following XML view template creates a 'Publications' view in the 'Medical' schema.

xquery version "1.0-ml";
import module namespace tde = "http://marklogic.com/xdmp/tde" 
        at "/MarkLogic/tde.xqy";

let $ClinicalView :=
<template xmlns="http://marklogic.com/xdmp/tde">
  <description>populates patients' data</description>
  <context>/Citation/Article</context>
  <rows>
    <row>
      <schema-name>Medical</schema-name>
      <view-name>Publications</view-name>
      <columns>
        <column>
          <name>ID</name>
          <scalar-type>long</scalar-type>
          <val>../ID</val>
        </column>
        <column>
          <name>ISSN</name>
          <scalar-type>string</scalar-type>
          <val>Journal/ISSN</val>
        </column>
        <column>
          <name>Volume</name>
          <scalar-type>string</scalar-type>
          <val>Journal/JournalIssue/Volume</val>
          <nullable>true</nullable>
        </column>
        <column>
          <name>Date</name>
          <scalar-type>string</scalar-type>
          <val>Journal/JournalIssue/PubDate/Year||'-'
             ||Journal/JournalIssue/PubDate/Month||'-'
             ||Journal/JournalIssue/PubDate/Day</val>
          <nullable>true</nullable>
        </column>
      </columns>
    </row>
  </rows>
</template>
return tde:template-insert("Template.xml", $ClinicalView)

JSON View Template

The following JSON view template creates a 'Publications' view in the 'Medical' schema.

declareUpdate();
var tde = require("/MarkLogic/tde.xqy");
var ClinicalView = xdmp.toJSON(

{
  "template":{
    "context":"/Citation/Article",
    "rows":[
      {
        "schemaName":"Medical",
        "viewName":"Publications",
        "columns":[
          {
            "name":"ID",
            "scalarType":"long",
            "val":"../ID"
          },
          {
            "name":"ISSN",
            "scalarType":"string",
            "val":"Journal/ISSN"
          },
          {
            "name":"Volume",
            "scalarType":"string",
            "val":"Journal/JournalIssue/Volume"
          },
          {
            "name":"Date",
            "scalarType":"string",
            "val":"Journal/JournalIssue/PubDate/Year||'-' \
                 ||Journal/JournalIssue/PubDate/Month||'-' \
                 ||Journal/JournalIssue/PubDate/Day"
          }
        ]
      }
    ]
  }
}
);

tde.templateInsert("Template.json", ClinicalView);

Creating Views from Multiple Templates

You can create a single view from multiple templates. For example, if you want to create a view to support more than one context or scope. The templates below create a Publications view with columns that are scoped for the two different documents shown in Example Documents. The result is that a single query on the Publications view will populate the relavent columns from each document. For example, the ‘Title' column will be populated with the value of the <ArticleTitle> element in the med1.xml file and the Title property in the med2.json file.

Note that both templates have the view-layout set to strict and that the ISSN, and Author columns are flagged as nullable.

Insert the first version of the Pulications view template as follows:

xquery version "1.0-ml";
import module namespace tde = "http://marklogic.com/xdmp/tde" 
        at "/MarkLogic/tde.xqy";

let $ClinicalView :=
<template xmlns="http://marklogic.com/xdmp/tde">
  <description>populates patients' data</description>
  <context>/Citation/Article</context>
  <rows>
    <row>
      <schema-name>Medical</schema-name>
      <view-name>Publications</view-name>
      <view-layout>sparse</view-layout>
      <columns>
        <column>
          <name>ISSN</name>
          <scalar-type>string</scalar-type>
          <val>Journal/ISSN</val>
          <nullable>true</nullable>
        </column>
        <column>
          <name>Title</name>
          <scalar-type>string</scalar-type>
          <val>ArticleTitle</val>
        </column>
        <column>
          <name>Volume</name>
          <scalar-type>string</scalar-type>
          <val>Journal/JournalIssue/Volume</val>
        </column>
        <column>
          <name>Date</name>
          <scalar-type>string</scalar-type>
          <val>Journal/JournalIssue/PubDate/Month||'/'
             ||Journal/JournalIssue/PubDate/Day||'/'
             ||Journal/JournalIssue/PubDate/Year</val>          
          <nullable>true</nullable>
        </column>
      </columns>
    </row>
  </rows>
</template>
return tde:template-insert("Template.xml", $ClinicalView)

Insert the second version of the Pulications view template as follows:

xquery version "1.0-ml";
import module namespace tde = "http://marklogic.com/xdmp/tde" 
        at "/MarkLogic/tde.xqy";

let $ClinicalView :=
<template xmlns="http://marklogic.com/xdmp/tde">
  <description>populates patients' data</description>
  <context>/Journal</context> 
  <rows>
    <row>
      <schema-name>Medical</schema-name>
      <view-name>Publications</view-name>
      <view-layout>sparse</view-layout>
      <columns>
        <column>
          <name>Volume</name>
          <scalar-type>string</scalar-type>
          <val>Issue</val>
        </column>
        <column>
          <name>Title</name>
          <scalar-type>string</scalar-type>
          <val>Title</val>
        </column>
        <column>
          <name>Date</name>
          <scalar-type>string</scalar-type>
          <val>Date</val>
          <nullable>true</nullable>
        </column>
        <column>
          <name>Author</name>
          <scalar-type>string</scalar-type>
          <val>Author</val>
          <nullable>true</nullable>
        </column>
      </columns>
    </row>
  </rows>
</template>
return tde:template-insert("Template2.xml", $ClinicalView)

To see the combined results, enter:

select * FROM Medical.Publications

The results should look like:

ISSNTitleVolumeDateAuthor
nullBone Density Studies1038/1/2009John Simson
0043-5341The Influence of Calcium on Cholesterol in Human Serum11812/7/1968null

Creating Views from Nested Templates

You can nest template views. The example in this section, though not based on a credible use case, does show how to nest three template views for 'medical' documents so that each child view is within the context of its parent view. The example also shows how variables can be defined in a parent template and then used in child templates. There is no limit to the nesting of template views.

The context for each nested view is as follows:

ViewContext
Publication/Citation
JournalIssue/Citation/Article/Journal/JournalIssue
PubDate/Citation/Article/Journal/JournalIssue/PubDate
<template xmlns="http://marklogic.com/xdmp/tde">
  <description>Views of the medical data set</description>
  <context>/Citation</context>
  <!-- Variables extracted at the current context level -->
  <vars>
    <var>
      <name>ID</name>     
      <val>./ID</val>
    </var>
    <var>
      <name>Status</name>
      <val>@Status</val>
    </var>
  </vars>
  <rows>
    <row>
      <schema-name>medical</schema-name>
      <view-name>Publication</view-name>
      <view-layout>sparse</view-layout>
          <columns>
             <column>
                <name>ArticleTitle</name>
                 <scalar-type>string</scalar-type>
                 <val>Article/ArticleTitle</val>
             </column>
             <column>
                <name>ISSN</name>
                 <scalar-type>string</scalar-type>
                 <val>Article/Journal/ISSN</val>
             </column>
          </columns>
    </row>
  </rows>
  <templates>
    <template>
      <!-- Nested child template -->
      <!-- context path relative to the parent context: /Citation -->
      <context>Article/Journal/JournalIssue</context>
        <rows>
           <row>
               <schema-name>medical</schema-name>
               <view-name>JournalIssue</view-name>
               <view-layout>sparse</view-layout>
               <columns>
                  <column>
                      <name>MedID</name>
                      <scalar-type>long</scalar-type>
                      <val>$ID</val>
                      <!-- referencing context var ID -->
                  </column>
                  <column>
                      <name>Volume</name>
                      <scalar-type>long</scalar-type>
                      <val>Volume</val>
                  </column>
                  <column>
                      <name>Issue</name>
                      <scalar-type>long</scalar-type>
                      <val>Issue</val>
                      <nullable>true</nullable>
                  </column>
               </columns>
           </row>
        </rows>
        <templates>
          <template>
          <!-- Nested child template -->
          <!-- context path relative to the parent context:
                /Article/Journal/JournalIssue -->
          <context>PubDate</context>
          <rows>
             <row>
               <schema-name>medical</schema-name>
               <view-name>PubDate</view-name>
               <view-layout>sparse</view-layout>
               <columns>
                   <column>
                      <name>Status</name>
                      <scalar-type>string</scalar-type>
                      <val>$Status</val>
                      <!-- referencing context var Status -->
                   </column>
                   <column>
                      <name>Year</name>
                      <scalar-type>long</scalar-type>
                      <val>Year</val>
                   </column>
                   <column>
                      <name>Month</name>
                      <scalar-type>string</scalar-type>
                      <val>Month</val>
                   </column>
                   <column>
                      <name>Day</name>
                      <scalar-type>long</scalar-type>
                      <val>Day</val>
                   </column>
               </columns>
             </row>
          </rows>
        </template>
       </templates>
    </template>
  </templates>
</template>
« Previous chapter
Next chapter »
Powered by MarkLogic Server 7.0-4.1 and rundmc | Terms of Use | Privacy Policy