XQuery/XQuery from SQL

The Scott/Tiger example

A classic database widely used in teaching Relational databases concerns Employees, Departments and SalaryGrades. In Oracle training material it is known by the demo username and password, Scott/Tiger.

These three tables converted to XML (via the XML add-in to Excel XQuery/Excel and XML ) are:

Employees XML Table MySQL

Departments XML Table MySQL

Salary Grades XML Table MySQL

A port of the Oracle SQL file to MySQL can be found here.

Execution environments

The eXist demo server is used for the XQuery examples. These are returned either as plain XML or converted to table format.

The equivalent SQL queries are executed on an w:MySQL server,also based at the University of the West of England in Bristol

Basic Queries

Counting Records

Task: How many Employees?

SQL: select count(*) from Emp; MySQL

XQuery: count(//Emp) XML

Task: How many Departments?

SQL: select count(*) from dept MySQL

XQuery: count(//Dept) XML

Selecting records

Task: Show all Employees with a salary greater than 1000

SQL: select * from emp where sal > 1000; MySQL

XQuery: //Emp[Sal>1000] XML Table

Task: Show all Employees with a salary greater than 1000 and less than 2000

SQL: select * from emp where sal between 1000 and 2000; MySQL

XQuery: //Emp[Sal>1000][Sal<2000] XML Table

Here, successive filter conditions replace the anded conditions implied by 'between'.

Although there is no 'between' function in XQuery, it is a simple matter to write one:

declare function local:between($value as xs:decimal, $min as xs:decimal, $max as xs:decimal) as xs:boolean {
  $value >= $min and $value <= $max
};

which simplifies the query to //Emp[local:between(Sal,1000,2000)] XML Table

and has the advantage that the conversion of Sal to a number is now implicit in the function signature.

Task: Show all employees with no Commission

SQL: select * from emp where comm is null; MySQL

XQuery: //Emp[empty(Comm/text())] XML Table

Note that empty(Comm) is not enough, since this is true only if the element itself is absent, which in this sample XML it is not.

XQuery: //Emp[empty(Comm)] XML

Task: Select the first 5 employees

SQL: select * from emp limit 5; MySQL

XQuery: //Emp[position() <=5]

XML Table

Selecting Columns

List Employee names and salaries

SQL: Select ename,sal from emp MySQL

Surprisingly, selecting only a subset of children in a node (pruning) is not supported in XPath.

//Emp/(Ename,Sal) XML retrieves the required elements, but the parent Emp nodes have been lost.

//Emp/(Ename|Sal) XML is better since it keeps the elements in sequence, but it does not return Emp nodes with only the Ename and Sal children as required.

//Emp/*[name(.) = ("Ename","Sal")] XML uses reflection on the element names.

XQuery:

    for $emp in //Emp 
    return 
       <Emp>
         {$emp/(Ename|Sal)}
       </Emp>

XML Table

Here an XQuery FLWOR expression is used to create a new EMP element from the original elements.

Computing values

Computing the Annual Salary

Task: Compute the Annual Salaries of all employees. The Annual Salary is computed from 12 times the Monthly salary plus Commission. Since commission may be null, it must be replaced by a suitable numeric value:

SQL: select 12 * sal + ifnull(comm,0) from emp; MySQL

XQuery: //Emp/(12*number(Sal)+(if(exists(Comm/text())) then number(Comm) else 0)) XML

The SQL function COALESCE is the same as IFNULL but will accept multiple arguments:

SQL: select 12 * sal + coalesce(comm,0) from emp; MySQL

XQuery: //Emp/(12*number(Sal)+ number((Comm/text(),0)[1])) XML

The lack of a schema in this simple example to carry information on the type of the items, leads to the need for explicit conversion of strings to numbers.

Note the XQuery idiom:

 (Comm/text(),0)[1] 

computes the first non-null item in the sequence, the counterpart of COALESCE.

Selecting and Creating Columns

Task: List the employee names with their Annual Salary.

SQL: select ename, 12 * sal + ifnull(comm,0) as "Annual Salary" from emp; MySQL

XQuery:

for $emp in //Emp 
return <Emp>
         {$emp/Ename}
         <AnnualSalary>
            {12*number($emp/Sal)+ 
             (if (exists($emp/Comm/text())) 
             then number($emp/Comm)
             else 0)
            }
         </AnnualSalary>
       </Emp> 

XML Table

Again we have the problem of tree-pruning, but now with added grafting, which again requires the explicit construction of an XML node.

SQL Operators

IN

Task: Show all employees whose Job is either ANALYST or MANAGER

SQL: select * from emp where job in ("ANALYST","MANAGER") MySQL

XQuery: //Emp[Job = ("ANALYST","MANAGER")]

XML Table

NOT IN

Task :Select all employees whose Job is not 'ANALYST' or 'MANAGER'

SQL: select * from emp where job not in ("ANALYST","MANAGER") MySQL

This doesn't work:

XQuery: //Emp[Job !=("ANALYST","MANAGER")] XML Table

The generalised equals here is always true since everyone is either not an ANALYST or not a MANAGER. This works:

XQuery: //Emp[not(Job =("ANALYST","MANAGER"))] XML Table

Distinct values

Task: Show the different Jobs which Employees have

MySQL: select distinct job from emp; MySQL

XQuery: distinct-values(//Emp/Job) XML

Pattern Matching

Task: List all Employees with names starting with "S"

MySQL: select * from emp where ename like "S%"; MySQL

XQuery: //Emp[starts-with(Ename,"S")] XML Table

See starts-with()

Task: List all Employees whose name contains "AR"

MySQL: select * from emp where ename like "%AR%"; MySQL

XQuery: //Emp[contains(Ename,"AR")] XML Table

See contains()

Task: List all Employees whose name contains "ar" ignoring the case

MySQL: select * from emp where ename like "%ar%"; MySQL

LIKE in SQL is case insensitive, but fn:contains() is not, so the case needs to be converted:

XQuery: //Emp[contains(upper-case(Ename),upper-case("ar"))] XML Table

See upper-case()

More complex patterns need regular expressions.

MySQL: select * from emp where ename regexp "M.*R"; MySQL

XQuery: //Emp[matches(Ename,"M.*R")] XML Table

See matches()

Similarly, SQL's REGEXP is case-insensitive, whereas additional flags control matching in the XQuery matches()

MySQL: select * from emp where ename regexp "m.*r"; MySQL

XQuery: //Emp[matches(Ename,"m.*r",'i')] XML Table

('i' makes the regex match case insensitive.)

Table Joins

Simple Inner joins

Task: Find the name of the department that employee 'SMITH' works in:

SQL :

select dept.dname 
from emp, dept 
where dept.deptno = emp.deptno  
   and ename='SMITH';

MySQL

XPath : //Dept[DeptNo = //Emp[Ename='SMITH']/DeptNo]/Dname

XML

Perhaps a FLWOR expression in XQuery would be more readable:

let $dept := //Emp[Ename='SMITH']/DeptNo
return //Dept[DeptNo = $dept ]/Dname

XML

Task: To find the names of all employees in Accounting

SQL:

select emp.ename 
from emp,dept 
where dept.deptno = emp.deptno 
  and dname='Accounting';

MySQL

XPath: //Emp[DeptNo = //Dept[Dname='Accounting']/DeptNo]/Ename

XML

XQuery:

let $dept := //Dept[Dname='Accounting']/DeptNo
return //Emp[DeptNo = $dept]/Ename

XML

Note that in this release of eXist, the order of the operands in the equality is significant - to be fixed in a later release.

XQuery: //Emp[Dname='Accounting'/DeptNo = //Emp/DeptNo]/Ename

XML

More complex Inner Join

Task: List the name of each Employee, together with the name and location of their department.

SQL:

  select ename, dname,location
    from emp, dept
   where emp.deptno = dept.deptno;

MySQL

Where elements must be selected from several nodes, XPath is insufficient and XQuery is needed:

XQuery:

This join could be written as:

  for $emp in //Emp
  for $dept in //Dept
  where $dept/DeptNo= $emp/DeptNo
  return
    <Emp>
      {$emp/Ename}
      {$dept/(Dname|Location)}
    </Emp> 

XML Table

But it would be more commonly written in the form of a sub-selection:

  for $emp in //Emp
  let $dept := //Dept[DeptNo=$emp/DeptNo]
  return
    <Emp>
      {$emp/Ename}
      {$dept/(Dname|Location)}
    </Emp> 

XML Table

Inner Join with Selection

Task: List the names and department of all Analysts

SQL:

  select ename, dname
    from emp, dept
   where emp.deptno = dept.deptno
     and job="ANALYST";

MySQL

XQuery:

  for $emp in //Emp[Job='ANALYST']
  let $dept := //Dept[DeptNo= $emp/DeptNo]
  return
    <Emp>
      {$emp/Ename}
      {$dept/Dname}
    </Emp> 

XML Table

1 to Many query

Task: List the departments and the number of employees in each department

SQL:

  select dname,
         (select count(*)
            from emp
           where deptno = dept.deptno
         ) as headcount
    from dept;

MySQL

XQuery:

  for $dept in //Dept 
  let $headCount := count(//Emp[DeptNo=$dept/DeptNo])
  return  
    <Dept>
       {$dept/Dname}
       <HeadCount>{$headCount}</HeadCount>
    </Dept>

XML Table

Theta (Inequality) Join

Task: List the names and salary grade of staff in ascending grade order

Grades are defined by a minimum and maximum salary.

SQL:

select ename, grade
  from emp, salgrade
 where emp.sal between salgrade.losal and salgrade.hisal;

MySQL

XQuery:

for $emp in //Emp
let $grade :=  //SalGrade[number($emp/Sal) > number(LoSal)][number($emp/Sal) < number(HiSal)]/Grade
order by $grade
return
  <Emp>
    {$emp/Ename}
    {$grade}
  </Emp> 

XML Table

Recursive Relations

The relationship between an employee and their manager is a recursive relationship.

Task: List the name of each employee together with the name of their manager.

SQL:

select e.ename, m.ename
  from emp e
  join emp m on e.mgr = m.empno

MySQL

XQuery:

for $emp in //Emp
let $manager := //Emp[EmpNo = $emp/MgrNo]
return
  <Emp>
    {$emp/Ename}
    <Manager>{string($manager/Ename)}</Manager>
  </Emp> 

XML Table

The XQuery result is not quite the same as the SQL result. King, who has no manager, is missing from the SQL inner join. To produce the same result in XQuery, we would filter for employees with Managers:

for $emp in //Emp[MgrNo]
let $manager := //Emp[EmpNo = $emp/MgrNo]
where $emp/MgrNo/text()
return
  <Emp>
    {$emp/Ename}
    <Manager>{string($manager/Ename)}</Manager>
  </Emp> 

XML Table

Alternatively, an outer join returns all employees, including King:

SQL:

select e.ename, m.ename
  from emp e
  left join emp m on e.mgr = m.empno

MySQL

Conversion to an organisational tree

The manager relationship defines a tree structure, with King at the root, her direct reports as her children and so on. A recursive function in XQuery solves this task.

XQuery:

 declare function local:hierarchy($emp) {
     <Emp name='{$emp/Ename}'>
       <Reports>
       {for $e in  //Emp[MgrNo = $emp/EmpNo]
        return
           local:hierarchy($e)
       }
       </Reports>
     </Emp>
 };
 
 local:hierarchy(//Emp[empty(MgrNo/text())])

XML

Conversion to a Department/Employee Hierarchy

For export, a single XML file could be created with Employees nested within Departments. This is possible without introducing redundancy or loss of data because the Dept/Emp relationship is exactly one to many.

XQuery:

<Company>
  {for $dept in //Dept
  return
    <Department>
      {$dept/*}   
      {for $emp in //Emp[DeptNo = $dept/DeptNo]  
      return $emp
      }
    </Department>
  }
</Company>

XML

With this simple approach, the foreign key DeptNo in Emp has been included but it is now redundant. The except operator is useful here:

<Company>
   {for $dept in //Dept
    return
       <Department>
          {$dept/*}   
          {for $emp in //Emp[DeptNo = $dept/DeptNo]  
           return 
              <Employee>
                  {$emp/* except $emp/DeptNo}
              </Employee>
          }
       </Department>
    }
</Company>

XML

Note that this assumes there are no attributes to be copied. If there are, these would be copied with $emp/@*

Working with the hierarchical data

This hierarchical data can be queried directly in XQuery.

Path to Employee

Almost all the queries remain the same (except for the change of element name to Employee). This is because the path used to select Emps in the Emp.xml document is //Emp and is now //Employee in the merged document. If a full path had been used (/EmpList/Emp), this would need to be replaced by /Company/Department/Employee

Simple Navigation

Task: To find the department name of employee 'Smith'

XQuery: //Employee[Ename='SMITH']/../Dname XML

Task: To find the names of employees in the Accounting department

XQuery: //Department[Dname='Accounting']/Employee/Ename XML

Department /Employee Join

The main changes are in queries which require a join between Employee and Departments because they are already nested and thus become navigation up (from Employee to Department ) or down (from Department to Employee) the tree.

many - one

The query to list the Employees and the location of their Department with separate documents was:

 for $emp in //Emp
 for $dept in //Dept
  where $dept/DeptNo=$emp/DeptNo
  return
    <Emp>
      {$emp/Ename}
      {$dept/(Dname|Location)}
    </Emp> 

XML Table

With one nested document, this becomes:

 for $emp in //Employee
 return
    <Employee>
      {$emp/Ename}
      {$emp/../Location}
    </Employee> 

XML Table using the parent access to move up the tree.

1 - many

To list departments and the number of employees in the separate tables is :

for $dept in //Dept 
let $headCount := count(//Emp[DeptNo=$dept/DeptNo])
return  
    <Dept>
       {$dept/Dname}
       <HeadCount>{$headCount}</HeadCount>
    </Dept>

XML Table

which becomes:

for $dept in //Department
let $headCount := count($dept/Employee)
return  
    <Department>
       {$dept/Dname}
       <HeadCount>{$headCount}</HeadCount>
    </Department>

XML Table

Summarising and Grouping

Summary data

Task: Show the number, average (rounded), min and max salaries for Managers.

SQL: SELECT count(*), round(avg(sal)), min(sal), max(sal) FROM emp WHERE job='MANAGER'; MySQL

XQuery:

   (count(//Emp[Job='MANAGER']),round(avg(//Emp[Job='MANAGER']/Sal)),min(//Emp[Job='MANAGER']/Sal),max( //Emp[Job='MANAGER']/Sal))

XML

Better to factor out the XPath expression for the subset of employess:

let $managers := //Emp[Job='MANAGER']
return (count($managers),round(avg($managers/Sal)),min($managers/Sal),max($managers/Sal))

XML

It would be better to tag the individual values computed:

let $managers := //Emp[Job='MANAGER']
return 
   <Statistics>
     <Count>{count($managers)}</Count>
     <Average>{round(avg($managers/Sal))}</Average>
     <Min>{min($managers/Sal)}</Min>
     <Max>{max($managers/Sal)}</Max>
   </Statistics>

XML

Grouping

Task: Show the number, average (rounded), min and max salaries for each Job.

SQL: SELECT job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp GROUP BY job; MySQL

In XQuery, grouping must be done by iterating over the groups. Each Group is identified by the Job and we can get the set (sequence) of all Jobs using the distinct-values function:

for $job in distinct-values(//Emp/Job)
let $employees  := //Emp[Job=$job]
return 
   <Statistics>
         <Job>{$job}</Job>
         <Count>{count($employees )}</Count>
         <Average>{round(avg($employees/Sal))}</Average>
          <Min>{min($employees/Sal)}</Min>
          <Max>{max($employees/Sal)}</Max>
     </Statistics>

XML Table

Hierarchical report

Task: List the departments , their employee names and salaries and the total salary in each department

This must generate a nested table.

SQL: ?

XQuery:

<Report>
  {
  for $dept in //Dept
  let $subtotal := sum(//Emp[DeptNo = $dept/DeptNo]/Sal)
  return
     <Department>
        {$dept/Dname}
        {for $emp in //Emp[DeptNo = $dept/DeptNo]
         return
            <Emp>
              {$emp/Ename}
              {$emp/Sal}
            </Emp>
         }
         <SubTotal>{$subtotal}</SubTotal>
      </Department>
   }
   <Total>{sum(//Emp/Sal)}</Total>
</Report>

XML

Note that the functional nature of the XQuery language means that each total must be calculated explicitly, not rolled up incrementally as might be done in an imperative language. This has the advantage that the formulae are explicit and independent and can thus be placed anywhere in the report, such as at the beginning instead of at the end:

<Report>
  <Total>{sum(//Emp/Sal)}</Total>
  {
  for $dept in //Dept
  let $subtotal := sum(//Emp[DeptNo = $dept/DeptNo]/Sal)
  return
     <Department>
         <SubTotal>{$subtotal}</SubTotal>
        {$dept/Dname}
        {for $emp in //Emp[DeptNo = $dept/DeptNo]
         return
            <Emp>
              {$emp/Ename}
              {$emp/Sal}
            </Emp>
         }
      </Department>
   }
 </Report>

XML

Restricted Groups

Task: Show the number, average (rounded), min and max salaries for each Job where there are at least 2 employees in the group.

SQL:

SELECT job, count(*), round(avg(sal)), min(sal), max(sal)
  FROM emp
 GROUP BY job
HAVING count(*) > 1;

MySQL

XQuery:

for $job in distinct-values(//Emp/Job)
let $employees := //Emp[Job=$job]
where count($employees) > 1
return 
   <Statistics>
      <Job>{$job}</Job>
      <Count>{count($employees )}</Count>
      <Average>{round(avg($employees /Sal))}</Average>
      <Min>{min($employees /Sal)}</Min>
      <Max>{max($employees /Sal)}</Max>
   </Statistics>

XML Table

Date Handling

Selecting by Date

Task: list all employees hired in the current millenium

SQL: SELECT * from job where hiredate >= '2000-01-01' MySQL

XQuery: //Emp[HireDate >= '2000-01-01']

Actually this comparison is a string comparison because of the lack of a schema to define HireDate as an xs:date.

XML Table