How to insert a xquery module in a Template file in MarkLogic

Question

I have written one xquery to get count of my records based on some conditions and would like to put the result as a column value in a Template document.

How can I call the xquery from the template in this case

Below is the Xquery which calls all documents from collection :GTM2_Shipment and gives count of records based on given condition.

 xquery version "1.0-ml";
fn:count(
   for $x in collection("GTM2_Shipment")
   let $currentdt := fn:current-date()
   let $sixmonthAgo := $currentdt - xs:yearMonthDuration("P6M")
   where ($x/*:Shipment/*:Ancillary/*:QuotePrice/text() != 0) and
   ($x/*:Shipment/*:ASN/*:TrackedItem/*:Consignment/*:ConsignmentHeader/*:CargoSummary/*:TransMode/text() = 'Road')
   and 
   (fn:substring($x/*:Shipment/*:ASN/*:TrackedItem/*:Consignment/*:Bookings/*:CargoBookingHeader/*:BookingCreateDt,0,11) gt
   (format-date($sixmonthAgo, "[Y0001]-[M01]-[D01]")))
   return $x
)

Example Template below where I want to add one more column named CountByRoad and add the above xquery and it refers the same collection -GTM2_Shipment:

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

if (xdmp:database-name(xdmp:database()) = "data-hub-final-SCHEMAS")  then          
    let $Custom_Shipment:=
   <template xmlns="http://marklogic.com/xdmp/tde">
  <path-namespaces>
    <path-namespace>
      <prefix>xmlns</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/Shipment</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns00</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/PurchaseOrderHeader</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns0</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/ConsignmentHeader</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns1</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/PurchaseOrderDrop</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns2</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/ItemDetail</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns5</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/TransportationUnitHeader</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns7</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/PurchaseOrderDelivery</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns9</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/BaseType</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns10</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/ASN</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns11</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/TransportationUnitHeader</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns13</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/ContainmentHeader</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns14</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/CargoBookingHeader</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ns15</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/Consignment</namespace-uri>
    </path-namespace>
    <path-namespace>
      <prefix>ps</prefix>
      <namespace-uri>http://www.globaltrademanagement_export.com/Shipment</namespace-uri>
    </path-namespace>
  </path-namespaces>
  <context>//ps:Shipment</context>
  <collections>
    <collection>GTM2_Shipment</collection>
  </collections>
  <rows>
    <row>
      <schema-name>GTM2_Shipment</schema-name>
      <view-name>Shipment_View</view-name>
      <view-layout>sparse</view-layout>
      <columns>
    <column>
      <name>Shipment_Ref</name>
      <scalar-type>string</scalar-type>
      <val>//ps:ShipmentRef</val>
    <nullable>true</nullable>
    </column>
    <column>
      <name>User</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns9:DocumentHeader/ns9:Version/ns9:CurrentVersion/ns9:User</val>
      <nullable>true</nullable>
    </column>
     <column>
      <name>PurchaseOrder</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:RefPurchaseOrders</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>Carrier_LocalCode</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:Carrier/ns9:LocalCode</val>
      <nullable>true</nullable>
    </column>
    
    <column>
      <name>Notify_PartyCode</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:Notify/ns9:Code</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>houseBill_Ref</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:HouseBillRef</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>primaryVessel</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:PrimaryVessel</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>Dept</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:Dept</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>Route</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:Route</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>transMode</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:CargoSummary/ns0:TransMode</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>OSP_PartyName</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:CargoSummary/ns0:OSP/ns9:PartyName</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>OSP_Code</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns0:ConsignmentHeader/ns0:CargoSummary/ns0:OSP/ns9:Code</val>
      <nullable>true</nullable>
    </column>
    
    <column>
      <name>Trans_UnitRef</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:Schedule/ns11:TransportationUnitHeader/ns11:PrimarytUnit/ns11:TransportUnit/ns11:TransUnitRef</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>POL_Name</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:Schedule/ns11:TransportationUnitHeader/ns11:PrimarytUnit/ns11:TransportUnit/ns11:PortOfLoading/ns9:PortName</val>
      <nullable>true</nullable>
    </column>
    <column>
      <name>POL_ETA_Sch_Dep</name>
      <scalar-type>dateTime</scalar-type>
      <val>//ns10:ASN/ns10:Schedule/ns11:TransportationUnitHeader/ns11:PrimarytUnit/ns11:TransportUnit/ns11:PortOfLoading/ns9:ScheduledDepartureDate</val>
      <nullable>true</nullable>
      <invalid-values>ignore</invalid-values>
    </column>
    <column>
      <name>POL_ETA_Act_Dep</name>
      <scalar-type>dateTime</scalar-type>
      <val>//ns10:ASN/ns10:Schedule/ns11:TransportationUnitHeader/ns11:PrimarytUnit/ns11:TransportUnit/ns11:PortOfLoading/ns9:ActualDepartureDate</val>
      <nullable>true</nullable>
      <invalid-values>ignore</invalid-values>
    </column>
    <column>
      <name>POL_ETA_Sch_Arr</name>
      <scalar-type>dateTime</scalar-type>
      <val>//ns10:ASN/ns10:Schedule/ns11:TransportationUnitHeader/ns11:PrimarytUnit/ns11:TransportUnit/ns11:PortOfLoading/ns9:ScheduledArrivalDate</val>
      <nullable>true</nullable>
      <invalid-values>ignore</invalid-values>
    </column>
    <column>
      <name>POL_ETA_Act_Arr</name>
      <scalar-type>dateTime</scalar-type>
      <val>//ns10:ASN/ns10:Schedule/ns11:TransportationUnitHeader/ns11:PrimarytUnit/ns11:TransportUnit/ns11:PortOfLoading/ns9:ActualArrivalDate</val>
      <nullable>true</nullable>
      <invalid-values>ignore</invalid-values>
    </column>
    <column>
      <name>POD_Name</name>
      <scalar-type>string</scalar-type>
      <val>//ns10:ASN/ns10:Schedule/ns11:TransportationUnitHeader/ns11:PrimarytUnit/ns11:TransportUnit/ns11:PortOfDischarge/ns9:PortName</val>
      <nullable>true</nullable>
      <invalid-values>ignore</invalid-values>
    </column>
    <column>
      <name>BookingCreateDt</name>
      <scalar-type>dateTime</scalar-type>
      <val>//ns10:ASN/ns10:TrackedItem/ns15:Consignment/ns15:Bookings/ns14:CargoBookingHeader/ns14:BookingCreateDt</val>
      <nullable>true</nullable>
    <invalid-values>reject</invalid-values>
    </column>

  <column>
      <name>Ancillary_QuotePrice</name>
      <scalar-type>decimal</scalar-type>
      <val>//ps:Ancillary/ps:QuotePrice</val>
      <nullable>true</nullable>
    <invalid-values>reject</invalid-values>
    </column>
      </columns>
    </row>
  </rows>
</template>
return
(

tde:template-insert("/GTM2/SHIPMENT_ShipmentView.xml",$Custom_Shipment),
      "template inserted")
      else ("Please select correct database.")

Can you provide an example of what you are talking about? It isn't clear. — Mads Hansen, Jun 28 '22 at 10:41
Thanks for response. I want to get count of e certain xml field value based upon different conditions and show them up in a template view . I have written separate xqueries for the different usecases and now want to induce the xquery inside my template or call the xquery from my template to reproduce the result of the count in columns mentioned in the template. Please let me know if I am not clear yet to you in the requirement. — Rashmita Purkayastha, Jun 28 '22 at 11:38
Minimal code examples are the best and easiest for explaining. https://stackoverflow.com/help/minimal-reproducible-example — Mads Hansen, Jun 28 '22 at 11:57
Thanks, I have edited the question with code examples, Please check and help me with your suggestions. — Rashmita Purkayastha, Jun 28 '22 at 12:13

score 0 · Accepted Answer · answered Jun 28 '22 at 12:40

0

I don't think you can do what you want.

https://docs.marklogic.com/guide/app-dev/TDE#id_71415

For performance and security reasons, your path expressions are limited to a subset of XPath. For more details, see Template Driven Extraction (TDE) in the XQuery and XSLT Reference Guide.

https://docs.marklogic.com/guide/app-dev/TDE#id_99178

Templates support a dialect using a subset of XQuery with limited functionalities where only a subset of functions are available.

More complex operations like looping, FLWOR statements, iterations, and XML construction are not supported within the dialect. The property axis property:: is also not supported.

answered Jun 28 '22 at 12:40

Mads Hansen

63,927
12
112
147

So is there a way to reproduce the value of the results from this xquery into a view , as I want to send this result to get used in visualization report. – Rashmita Purkayastha Jun 28 '22 at 12:44
Possibly using https://docs.marklogic.com/op.fromSearch and joining to the TDE records – Mads Hansen Jun 28 '22 at 12:53
1

Well.. there is always a way to accomplish things.. Marklogic has a tool for almost anything. But sometimes it's like rummaging through a toolbox that has been thrown around and shaken - difficult to see the tool that you need. As of recent releases of ML, you can also deploy optic plans to the schemas database. So in that case, you could likely create an optic plan for your aggregate needs. – David Ennis -CleverLlamas.com Jun 29 '22 at 09:52
1

Hi @DavidEnnis, thanks, Using the optic plan for now. – Rashmita Purkayastha Jun 29 '22 at 16:05
Great to hear that you found a solution! – David Ennis -CleverLlamas.com Jun 29 '22 at 20:14

How to insert a xquery module in a Template file in MarkLogic

1 Answers1