CODEWITHSUNDEEP
marklogic
user10520244
user10520244

Reputation: 21

How to create a TDE for normal JSON with no hierarchy

I am trying to create a TDE in MarkLogic for a JSON file.

Purpose is to create a template and then extract data out of MarkLogic to create reports in BI.

Sample document in Marklogic:

{
"filename": "StudentDetails.txt", 
"auditId": "xxxx", 
"FileInputTimestamp": "2018-11-14T15:47:40.074", 
"FileProcessedTimestamp": "2018-11-14T15:47:40.236", 
}

**TDE i tried & i am able to validate it, but the JSON document doesnt have a hierarchy & i am not able to specify the context as root node. **

xquery version "1.0-ml";
let $my-first-TDE:=
  <template xmlns="http://marklogic.com/xdmp/tde">
    <context>/</context>
    <collections>
      <collection>ods_fms_file_audit</collection>
    </collections>
    <rows>
      <row>
        <schema-name>File_audit</schema-name>
        <view-name>ABaC_View</view-name>
        <columns>
          <column>
            <name>FILE_NAME</name>
            <scalar-type>string</scalar-type>
            <val>filename</val>
          </column>
          <column>
            <name>AUDIT_ID</name>
            <scalar-type>string</scalar-type>
            <val>auditId</val>
          </column>
        </columns>
      </row>
    </rows>
  </template>
return
  tde:validate( 
    $my-first-TDE 
  )

**This will extract the template with the document i pass & i get empty result set. Problem being, i am not able to specify the root node in the plain json. **

xquery version "1.0-ml";
let $my-first-TDE:=
  <template xmlns="http://marklogic.com/xdmp/tde">
    <context>/</context>
    <collections>
      <collection>ods_fms_file_audit</collection>
    </collections>
    <rows>
      <row>
        <schema-name>File_audit</schema-name>
        <view-name>ABaC_View</view-name>
        <columns>
          <column>
            <name>FILE_NAME</name>
            <scalar-type>string</scalar-type>
            <val>filename</val>
          </column>
          <column>
            <name>AUDIT_ID</name>
            <scalar-type>string</scalar-type>
            <val>auditId</val>
          </column>
        </columns>
      </row>
    </rows>
  </template>
return
  tde:node-data-extract( 
    fn:doc( "/file/StudentDetails.json" ), 
    $my-first-TDE 
  )

Also, would be great if i can get how can i extract and connect the marklogic output to POwer BI (or) Is there any provision to run a .xqy that generates my report and extract the data out from ML.

Upvotes: 1

Views: 163

Answers (2)

user10520244
user10520244

Reputation: 21

I was able to create a TDE for documents which did not have a root node by specificying a \ in the context part of TDE format.

Able to query them also in SQL window for Marklogic.Thanks all for helping me out in this.

Upvotes: 1

ehennum
ehennum

Reputation: 7335

The typical approach is to specify a property for the context as in

/filename

and then to specify the paths for the vals relative to the context as in

../filename

The reason for this practice is that the context plays an important role in determining which documents are indexed by the template. If the context is merely

/

then the template would have to attempt to index all documents, which would add a lot of cost to persistence (because indexing happens at persistence time).

If I recall correctly, recent versions of MarkLogic 9 will accept a root context if a collection or directory is also specified, but specifying a context with a step remains a best practice.

Hoping that helps,

Upvotes: 2

Related Questions