Search code examples
xmlmarklogicmarklogic-8marklogic-9

How to get TDE data from header in a harmonized document in FINAL database

I am creating a TDE document wherein I need to get data from the headers section of a harmonized document as well as to get values from the main section of the document (work order).

However, it seems that that my current tde context value is not displaying the expected value. Any thoughts on why is this happening in my current TDE code?

I tried many codes suchs as the one below:

//es:headers/ProjectSpecificData/WorkOrderStatusHistory/StatusHistory/StatusHistory

But it not gettng any data from the headers

Sample Harmonized Document in FINAL database

<?xml  version="1.0" encoding="UTF-8"?>
<envelope xmlns="http://marklogic.com/entity-services">
<headers>
<sourceURI xmlns="">/JDEdwards/CMMS/Sample/WorkOrder/DASH_JDEWO/7044389</sourceURI>
<ProjectSpecificData xmlns="http://testing.com/ads/project">
    <CompletedDate xmlns="http://testing.com/ads/project/fwe">2018-06-29</CompletedDate>
    <WorkOrderStatusHistory xmlns="http://testing.com/ads/project/fwe">
        <StatusHistory>
        <StatusHistory><UpdatedBy>RFCM</UpdatedBy>
        <UpdatedDate>2018/06/24</UpdatedDate>
        <StatusCode>55</StatusCode>
        <StatusDesc>Scheduled</StatusDesc>
        <StatusEffectiveDate>2018-06-24</StatusEffectiveDate>
        <StatusEndDate>2018-06-29</StatusEndDate></StatusHistory>
        <StatusHistory><UpdatedBy>GPFW</UpdatedBy>
        <UpdatedDate>2018/06/23</UpdatedDate>
        <StatusCode>10</StatusCode>
        <StatusDesc>Job Planning</StatusDesc>
        <StatusEffectiveDate>2018-06-23</StatusEffectiveDate>
        <StatusEndDate>2018-06-23</StatusEndDate></StatusHistory>
        <StatusHistory><UpdatedBy>COLO</UpdatedBy>
        <UpdatedDate>2018/07/01</UpdatedDate>
        <StatusCode>60</StatusCode>
        <StatusDesc>Daily Work in Progress</StatusDesc>
        <StatusEffectiveDate>2018-06-29</StatusEffectiveDate>
        <StatusEndDate>2018-06-29</StatusEndDate></StatusHistory>
        <StatusHistory><UpdatedBy>GPFW</UpdatedBy>
        <UpdatedDate>2018/06/22</UpdatedDate>
        <StatusCode>05</StatusCode>
        <StatusDesc>Ready for Approval</StatusDesc>
        <StatusEffectiveDate>2018-06-22</StatusEffectiveDate>
        <StatusEndDate>2018-06-23</StatusEndDate></StatusHistory>
        <StatusHistory><UpdatedBy>GPFW</UpdatedBy>
        <UpdatedDate>2018/06/24</UpdatedDate>
        <StatusCode>50</StatusCode>
        <StatusDesc>Ready to Schedule</StatusDesc>
        <StatusEffectiveDate>2018-06-23</StatusEffectiveDate>
        <StatusEndDate>2018-06-24</StatusEndDate></StatusHistory>
        <StatusHistory><UpdatedBy>JPGS</UpdatedBy>
        <UpdatedDate>2018/06/14</UpdatedDate>
        <StatusCode>01</StatusCode>
        <StatusDesc>Work Request</StatusDesc>
        <StatusEffectiveDate>2018-06-14</StatusEffectiveDate>
        <StatusEndDate>2018-06-22</StatusEndDate></StatusHistory>
        <StatusHistory><UpdatedBy>COLO</UpdatedBy>
        <UpdatedDate>2018/07/01</UpdatedDate>
        <StatusCode>65</StatusCode>
        <StatusDesc>Completed Ready for Review</StatusDesc>
        <StatusEffectiveDate>2018-06-29</StatusEffectiveDate>
        <StatusEndDate/></StatusHistory>
        </StatusHistory>
        </WorkOrderStatusHistory>
    </ProjectSpecificData>
    </headers>
<triples>
<instance>
<info>
<title>WorkOrder</title>
<version>1.0.1</version>
</info>
<WorkOrder entityExtVersion="1.0.1" entityVersion="1.0.1" entity="WorkOrder" defaultConfidence="95.00" xmlns="">
<WorkOrderCode sourcesystem="Testing" confidence="95.00">7044389</WorkOrderCode>
</WorkOrder>
</instance>
<attachments>
</attachments>
</envelope>

Current TDE Document code:

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

let $workordertde:=
<tde:template xmlns:tde="http://marklogic.com/xdmp/tde">
<tde:context>//es:headers/ProjectSpecificData/WorkOrderStatusHistory/StatusHistory/StatusHistory</tde:context>
<tde:path-namespaces>
<tde:path-namespace>
<tde:prefix>es</tde:prefix>
<tde:namespace-uri>http://marklogic.com/entity-services</tde:namespace-uri>
</tde:path-namespace>
</tde:path-namespaces>
<tde:collections>
<tde:collection>Collection</tde:collection>
</tde:collections>
<tde:enabled>true</tde:enabled>
<tde:rows>
<tde:row>
<tde:schema-name>TestSchema</tde:schema-name>
<tde:view-name>TestView</tde:view-name>
<tde:view-layout>sparse</tde:view-layout>
<tde:columns>
<tde:column>
<tde:name>WorkOrderID</tde:name>
<tde:scalar-type>string</tde:scalar-type>
<tde:val>//es:instance[es:info/es:version = "1.0.1"]/WorkOrder/WorkOrderNo</tde:val>
<tde:invalid-values>ignore</tde:invalid-values>
</tde:column>
<tde:column>
<tde:name>WOStatus</tde:name>
<tde:scalar-type>string</tde:scalar-type>
<tde:val>fn:concat(StatusCode, " - ", StatusDesc)</tde:val>
<tde:nullable>true</tde:nullable>
</tde:column>
<tde:name>BeginDate</tde:name>
<tde:scalar-type>string</tde:scalar-type>
<tde:val>StatusEffectiveDate</tde:val>
<tde:nullable>true</tde:nullable>
</tde:column>
</tde:columns>
</tde:row>
</tde:rows>
</tde:template>

Actual Results: When I test the TDE for a single document, it is not showing any values:

{
"/WorkOrder/JDEdwards/130008HU52-7044389.xml": [
]
}

Expected Results:

I expect the TDE to displau the values I am trying to get from the headers (StatusHistory data) as well as in the main section (WorkOrdercCode).

Edit:

Looks like the function who is returning the value of status history is returning a string value. I tried to remove the toString() call but it is not working. Any thoughts on the code?

function getStatusHistory(workOrder) { 
  var status = [];

  let statusHistory =  cts.search(cts.andQuery([
            cts.collectionQuery('Collection1'),
            cts.collectionQuery('Collection2'),
            //cts.collectionQuery('Collection3'),
            cts.elementWordQuery(fn.QName('http://www.testing.com/ads/JDEdwards/wostatushistory', 'F1DOCO'), workOrder)
        ])); 

  if(fn.count(statusHistory) <= 0) return "";

  let builder;
  let doc = new NodeBuilder();
  doc.startElement('StatusHistory');

  for (const item of statusHistory) {

    builder = new NodeBuilder();
    builder.startElement('StatusHistory');

    //Audit
    builder.addElement('UpdatedBy',sfh.ensureStringResult(fn.normalizeSpace(hl.elementText(item, "F1USER", true))));
    builder.addElement('UpdatedDate',sfh.ensureStringResult(fn.normalizeSpace(hl.elementText(item, "F1UPMJ", true))));


    //Status
    let statusCode = fn.normalizeSpace(hl.elementText(item, "F1EWST", true));
    builder.addElement('StatusCode',sfh.ensureStringResult(statusCode));
    builder.addElement('StatusDesc',sfh.ensureStringResult(getUDCDescription("00", "SS", statusCode)));

    builder.addElement('StatusEffectiveDate',sfh.ensureStringResult(fn.replace(fn.normalizeSpace(hl.elementText(item, "F1EFTB", true)),"/","-")));
    builder.addElement('StatusEndDate',sfh.ensureStringResult(fn.replace(fn.normalizeSpace(hl.elementText(item, "F1EFTE", true)),"/","-")));

    builder.endElement();
    doc.addElement('StatusHistory', builder.toNode().xpath('/node()').toString());

  }

  doc.endElement();

  return doc.toNode();
  //return status;
}
Solution

  • The ProjectSpecificData element is in the http://testing.com/ads/project namespace, but you didn't refer to that namespace in your context path. Try:

    <tde:template xmlns:tde="http://marklogic.com/xdmp/tde">
    <tde:context>//es:headers/ads:ProjectSpecificData/fwe:WorkOrderStatusHistory/fwe:StatusHistory/fwe:StatusHistory</tde:context>
    <tde:path-namespaces>
        <tde:path-namespace>
            <tde:prefix>es</tde:prefix>
            <tde:namespace-uri>http://marklogic.com/entity-services</tde:namespace-uri>
        </tde:path-namespace>
        <tde:path-namespace>
            <tde:prefix>ads</tde:prefix>
            <tde:namespace-uri>http://testing.com/ads/project</tde:namespace-uri>
        </tde:path-namespace>
        <tde:path-namespace>
            <tde:prefix>fwe</tde:prefix>
            <tde:namespace-uri>http://testing.com/ads/project/fwe</tde:namespace-uri>
        </tde:path-namespace>
    </tde:path-namespaces>
    ...
</tde:template>

    edit: response to a comment so that I can use formatting:

    Since you're having trouble getting values, there's probably still something wrong with your path. It will be easier to work through that in Query Console working with the paths directly than in TDE. In QC, set up the following:

    declare namespace es = "http://marklogic.com/entity-services";
declare namespace ads = "http://testing.com/ads/project";
declare namespace fwe = "http://testing.com/ads/project/fwe";

let $doc := (: your doc :)
let $context := $doc//es:headers/ads:ProjectSpecificData/fwe:WorkOrderStatusHistory/fwe:StatusHistory/fwe:StatusHistory
return $context

    Did you get anything back? If so, cool, now try returning the individual pieces of data you want. If not, start removing elements from the end of the context path until you do, then figure out what's wrong with the path.

    Once you've done that, you should be able to get your TDE template correct.