Search code examples
marklogicmarklogic-9

MarkLogic SQL Collation not used

I am getting different results using XQuery and SQL using a case insensitive collation.

I've got two app servers, a http and a odbc server, configured. Both using the same collation http://marklogic.com/collation/en/S1, which should be caseinsensitive.

Following XQueries return the correct value:

xquery version "1.0-ml";
/table[column="Content"];
xquery version "1.0-ml";
/table[column="content"]

=> Both return the searched document.

Following SQL Query does not return the correct value:

select * from table
where column = 'Content';

But this does:

select * from table
where column = 'content';

Is there anything else i need to configure in order to use a caseinsensitive collation with SQL? (except having a odbc server with that collation)

Solution

  • I believe MarkLogic indexes documents with a collation for the relational views.

    If you are using templates to creates views of the documents you may be able to match the collations by setting collation property at rows/row/columns/column/collation in the template definition.

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