SQL to MarkLogic mapping

Jason Hunter and Eric Bloch
Last updated 2012-09-28

Inspired by the SQL to Mongo Mappings, we've add some deets for those who are coming to MarkLogic and XQuery from a SQL world.


Terminology

SQL term MarkLogic term
database database
partition forest
table collection (or directory)
index index
row document
column element or attribute
join embedding or linking
primary key document URI

Queries

SQL statement MarkLogic XQuery expression *
CREATE TABLE USERS (a Number, b Number)

Not necessary

ALTER TABLE users ADD ...

Not necessary

INSERT INTO USERS VALUES(3,5)
document-insert("/foo.xml", 
  <user>
    <a>3</a>
    <b>5</b>
  </user>
)
SELECT a,b FROM users
/user/(a|b)
SELECT * FROM users
/user
SELECT * FROM users WHERE age=33
/user[age = 33]
SELECT a,b FROM users WHERE age=33
/user[age = 33]/(a|b)
SELECT * FROM users WHERE age=33 
 ORDER BY name
for $user in /user[age = 33]
order by $user/name
return $user
SELECT * FROM users WHERE age>33
/user[age > 33]
SELECT * FROM users WHERE age!=33
/user[age != 33]
SELECT * FROM users 
 WHERE name LIKE "%Joe%"
(: text substring, matches "Joey" :)
/user[fn:contains(name, "Joe")]   

(: full word match :)
/user[cts:contains(name, "Joe")]  
SELECT * FROM users 
 WHERE name LIKE "Joe%"
/user[cts:contains(name, "Joe*")]
SELECT * FROM users 
 WHERE age>33 AND 
       age<=40
/user[age > 33 and age <= 40]
SELECT * FROM users 
 ORDER BY name DESC
for $user in /user
order by $user/name descending
return $user
SELECT * FROM users 
 WHERE a=1 AND 
       b='q'
/user[a = 1][b = 'q']
SELECT * FROM users 
 LIMIT 10 SKIP 20
/user[21 to 30]
SELECT * FROM users WHERE a=1 or b=2
/user[a = 1 or b = 2]
SELECT * FROM users LIMIT 1
(/user)[1]
SELECT order_id FROM 
 orders o, order_line_items li 
 WHERE li.order_id=o.order_id 
 AND   li.sku=12345
/items[sku = 12345]/order_id
SELECT customer.name FROM customers,orders 
 WHERE orders.id="q179" 
 AND   orders.custid=customer.id 
/customer
 [id = fn:doc("/order/q179")/custid]/name
SELECT DISTINCT last_name FROM users
(: standard :)
fn:distinct-values(/user/last_name)          
(: optimized :)
cts:element-values(xs:QName("last_name")) 
SELECT COUNT(*y) FROM users
estimate(/user)
SELECT COUNT(*y) FROM users 
 WHERE AGE > 30
estimate(/user[age > 30])
SELECT COUNT(AGE) from users
estimate(/user/age)
CREATE INDEX myindexname ON users(name) 

Not necessary

CREATE INDEX myindexname 
 ON users(name, ts DESC) 

Not necessary

EXPLAIN SELECT * FROM users WHERE z=3
plan(/user[z = 3])
UPDATE users SET a=1 WHERE b='q'
node-replace(/user[b = 'q']/a, <a>1</a>)
UPDATE users SET a=a+2 WHERE b='q'
for $a in /user[b = 'q']/a
return node-replace($a, <a>{$a + 2}</a>)
DELETE FROM users WHERE z="abc"
(: auto iterates :)
document-delete(/user[z = "abc"])

* The expression must be prefaced by the following:

xquery version "1.0-ml"; 
declare default function namespace "http://marklogic.com/xdmp";

Contents

Comments