SQL to MarkLogic mapping

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

How do SQL concepts map to MarkLogic? How are queries different? Take a look!


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