XQuery/XQuery from SQL

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>

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>

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")]

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';

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

Perhaps a FLWOR expression in XQuery would be more readable:

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

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';

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

XQuery:

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

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

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;

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>

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>

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";

XQuery:

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

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;

XQuery:

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

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;

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>

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

XQuery:

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

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>

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

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())])

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>

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>

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>

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>

which becomes:

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

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))

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))

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>

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>

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>

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>

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;

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>

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.