TDE - Remove schema name and view name in column name

Question

I'm trying to create an simple SQL TDE template for XML documents in Marklogic DB.

Sample template:

<template xmlns="http://marklogic.com/xdmp/tde">
<context>/match</context>
<collections>
<collection>source1</collection>
</collections>
<rows>
<row>
<schema-name>soccer</schema-name>
<view-name>matches</view-name>
<columns>
<column>
<name>id</name>
<scalar-type>long</scalar-type>
<val>id</val>
</column>
<column>
<name>document</name>
<scalar-type>string</scalar-type>
<val>docUri</val>
</column>
<column>
<name>date</name>
<scalar-type>date</scalar-type>
<val>match-date</val>
</column>
<column>
<name>league</name>
<scalar-type>string</scalar-type>
<val>league</val>
</column>
</columns>
</row>
</rows>
</template>

I have inserted a sample document in the content database and the above template into Schemas database.

Now I'm trying to run a SQL query using XQuery as below:

xdmp:sql("select * from matches","map")

The above query returns the below response

soccer.matches.id	soccer.matches.document	soccer.matches.date	soccer.matches.league
1234567	/soccer/match/1234567.xml	2016-01-12	Premier

In json format it is,

[
{
"soccer.matches.id": 1234567, 
"soccer.matches.document": "/soccer/match/1234567.xml", 
"soccer.matches.date": "2016-01-12", 
"soccer.matches.league": "Premier"
}
]

In the above response the key name is returned as schema-name.view-name.column_name.E.g.,soccer.matches.id But I need the key name to have only the column name.E.g., "id": 1234567

Could anyone please share the easiest way to achieve this in XQuery?

Answer 1

You could probably se the names in the SQL statement using AS. You could always iterate over thr the results in a FLWOR statement to modify your results.

However, I suggest that you use the optic API and tune the results as you wish.

Using an op:from-view would be the suggested course of action. You could still use op:from-sql(). In both cases, experiment with the qualifier parameter including omitting it . For the final results, you can use an op:as() with op:select() or op:group-by() to define exactly what you want.

Optic API reference: https://docs.marklogic.com/guide/app-dev/OpticAPI

Optic Functions: https://docs.marklogic.com/op