XQuery Create where clause based on xml structure as a kind of dynamic where clause

Question

this is about XQuery - I am using MarkLogic as Database.

I have data as in the following example:

<instrument name="myTest1" id="test1">
   <daten>
      <daily>
         <day date="2016-02-05">
            <screener>
               <column name="i1">
                  <value>1</value>
                  <bg>red</bg>
               </column>
               <column name="i2">
                  <value>1</value>
                  <fg>lime</bg>
               </column>
               <column name="i4">
                  <fg>black</bg>
               </column>
            </screener>
         </day>
      </daily>
   </daten>
</instrument>

I have many instruments, and each one has an entry for each day in the daily element, and inside screener, there can be manz columns, all with different names. Some screeners include more columns than others. Each column can include a value element, a bg element and a fg element.

I want to search for instruments that fullfill specific criteria about what kind of columns do have children with specific values. Example: I want a sequence of all instruments, that for a given day, have a value 1 for column i1 and that have a fg black for column i2

Since I have many different of those conditions, I would not like to hardcode them in XQuery where clauses. I did that for a few and it works, but the code gets a lot of duplications and is hard to maintain.

My question is, is it possible to build a where clause in a FLOWR statement programatically, meaning, based on another xml structure, which could look like this:

<searchpatterns>
  <pattern name="test1">
     <c>
             <name>i1</name>
             <element>value</element>
             <value>1</value>
          </c>
          <c>
             <name>i2</name>
             <element>fg</element>
             <value>red</value>
             <modifier>not</modifier>
          </c>
  </pattern>
</searchpatterns>

which would find those instruments, where the screener has a column i1 which itself has a value of 1, and also it must not have column i2 with a fg of red.

When I do it the normal way I query my date like this:

for $res in doc()/instrument
   where $res/daten/daily/day[@date="2016-02-05"]/screener/column[@name="i1"]/value/text()="1"
      and res/daten/daily/day[@date="2016-02-05"]/screener/column[@name="i2"]/fg/text()!="red"

This kind of where clause I want to generate based on an XML structure.

I did some research of the MarkLogic inbuilt cts:search function and a lot of stuff around it but it seems to be for something else (more user interactive searching)

If you have a hint to point me in the right direction, if what I want is even possible, I would very much appreciate it.Thanks!

ehennum ehennum · Accepted Answer · 2016-02-10T16:44:35

The doc()/instrument XPath asks for every document with an instrument element and then filters those documents.

Where possible, it's usually better in MarkLogic to model the documents so you can use the indexes to retrieve as few documents as possible. It's also usually better to use cts:search() instead of XPath to generate the sequence so you are working directly with the indexes.

In this case, you might consider using the values of the name attribute as elements instead of the generic "column." You could then generate a cts:element-query that matches the name containing a cts:element-value-query that matches the value within the name.

Hoping that helps,

XQuery Create where clause based on xml structure as a kind of dynamic where clause

2 Answers