Description
Objectives
To practice the following aspects of XML:
• XML Schema.
• XPath Expressions.
• XQuery Language.
1. Installation
Install JRE and XML Editor by running the following commands.
Installation of JRE
1 sudo apt – get update
2 sudo apt – get install default – jre
Download XML Editor and Install
Installation of XML Editor
1 cd ~/ Downloads
2 wget http: // editix . com / download / editix2017 . zip
3 unzip -d editix2017 editix2017 . zip
Run the editor
1 sh ~/ Downloads / editix2017 / bin / editix . sh
From the menu bar, select F ile− > O penDocument and choose personal.xml
from the “ed itix2017/samples/xml” folder.
Review the content of the XML file personal.xml and its XML Schema document
personal.xsd.
Notice how the schema (.XSD) defines how elements can be validly constructed.
The schema defines:
1. Which elements and attributes are permitted and in which order. Otherwise,
an XML file is a relatively free set of elements and attributes.
2. The data types, default, and fixed values for elements and attributes.
2
2. XPath Expressions
XPath expressions allow us to select fragments of an XML document that meet
some search criteria. The expressions consist of a path to the elements or attributes
of interest and, optionally, conditions to be met in filtering the results.
• From the course shell download CompanyDB XML files: Compan y.xml and
compan y.xsd
• Open Compan y.xml from editix2017 application.
• Open the XPath Panel by selecting from the menu X ML− > X Pathview
• Write the following commands and observe the results
Practice XPath expressions
1 / companyDB / departments / department
2 / companyDB / employees / employee / @supervisor
3 / companyDB / employees / employee / lname / text ()
4 / companyDB / employees / employee /*
5 / companyDB / employees / employee / @*
6 // dob
7 / companyDB / employees / employee [7]
8 / companyDB / employees / employee [1]/ dependents /
dependent [2]
9 / companyDB / employees / employee [1]/ dependents /
dependent [ last ()]
10 / companyDB / employees / employee [ position () < =3]
11 / companyDB / employees / employee [ minit =”E”]
12 / companyDB / projects / project [
@controllingDepartment >6]
13 / companyDB / employees / employee [starts – with ( lname ,”
S”)]
14 / companyDB / employees / employee [ contains ( address ,”
Philadelphia “)]
15 // employee [ @worksFor =7 and sex =”M” and dependents
/ dependent [sex =”M”]]
16 / companyDB / departments / department / dname / text () |
/ companyDB / projects / project / pname / text ()
17 / companyDB / employees / employee [ @worksFor =
ancestor::companyDB / departments / department [
dname =” Administration “]/ @dno ]
3
3. XQuery Statements
XQuery statements allow us to construct a query program, similar to SQL, to yield
elements and attributes of interest from an XML document. These statements
are composed of F or, Let, Where, Order B y, and Return clauses called FLWOR
queries. (A letter in the acronym stands for the first letter of each type of clause).
• Open the XQuery Panel by selecting from the menu XML -> XQuery builder.
• Write the following cpmmands and observe the results.
Practice XQuery statements
1 let $ d: = doc (” company .xml”)
2 return $ d // companyDB / employees / employee [2]
3
4 (2*3) -(8*7)
5
6 concat (” Hello “,” World “)
7
8 matches (” Monday “,” ^.* da .*$ “)
9
10 current – time ()
11
12 let $ list: =(1 ,5 ,10 ,12 ,15)
13 return count ($ list )
14
15 let $ d: = doc (” company .xml”)
16 return $ d // companyDB / employees / employee [2]
17
18 let \$ d: = doc (” company .xml”) // employee [ @worksFor =6
]
19 return
20 < dept6Salary >{\$ d/ salary }
21 </ dept6Salary >
4
3.1 FLWOR Queries
Get all projects.
1 let $ d: = doc (” company .xml”) for $ p in
2 $ d / companyDB / projects / project
3 return $ p
Get distinct project numbers of projects in which employees work.
1 < projects >
2 {
3 let $ d: = doc (” company .xml”)
4 for $ p in distinct – values (
5 $ d / companyDB / employees / employee / projects / worksOn /
@pno ) return
6 < project >{$ p}</ project >
7 }
8 </ projects >
using order by clause
1 < projects >
2 {
3 let $ d: = doc (” company .xml”)
4 for $ p in distinct – values (
5 $ d / companyDB / employees / employee / projects / worksOn /
@pno ) order
6 by number ($ p ) return < project >{$ p}
7 </ project >
8 }
9 </ projects >
5
Get social security numbers of employees whose last name starts with “S”.
1 let $ d: = doc (” company .xml”) for $ e in
2 $ d / companyDB / employees / employee
3 where starts – with ($ e / lname ,”S”)
4 return < sssn >{$ e/ @ssn }</ sssn >
Get last names and first names of employees in the “Research” department.
1 let $ d: = doc (” company .xml”)
2 let
3 $ r: =$ d / companyDB / departments / department [ dname =”
Research “]
4 for $ e in $ d / companyDB / employees / employee where
5 $ e / @worksFor =$ r / @dno
6 return
7 < ResearchEmp >{$ e / lname }{$ e / fname }</ ResearchEmp >
Get employees who work more than 40 hours.
1 let $ d: = doc (” company .xml”) for $ e in
2 $ d / companyDB / employees / employee where
3 sum ($ e/ projects / worksOn / @hours ) >40.0 return
4 < OverWorkedEmp >{$e / lname }
5 {$ e / fname }< TotalHours >{sum ($ e / projects / worksOn /
@hours )}
6 </ TotalHours >
7 </ OverWorkedEmp >
Get last names of employees without dependents
1 let $ d: = doc (” company .xml”)
2 let $ empsWithDeps : =
3 $ d / companyDB / employees / employee [ dependents ] for $
e in
4 $ d / companyDB / employees / employee where
5 empty ( index – of ($ empsWithDeps ,$ e ) )
6 return $ e/ lname
6
Get department names and the total number of employees working in the
department
1 let $ d: = doc (” company .xml”) for $ r in
2 $ d / companyDB / departments / department return
3 < deptNumEmps >{$ r / dname }
4 < numEmps >{ count ( tokenize ($ r / employees / @essns ,”\s+
“) )}
5 </ numEmps >
6 </ deptNumEmps >
Get last names of employees who work for a project located in “Houston”.
1 < empsWorkingOnHoustonProjects >
2 { distinct – values ( let
3 $ d: = doc (” company .xml “)
4 for $ r in $ d / companyDB / projects / project [ plocation
=” Houston “]
5 return
6 for $ e in $ d / companyDB / employees / employee where
7 exists ( index – of ($ r / workers / worker / @essn ,$ e / @ssn ) )
return
8 $ e / lname
9 )
10 }
11 </ empsWorkingOnHoustonProjects >
Get last names of employees with dependents.
1 let $ d: = doc (” company .xml”)
2 for $ e in $ d / companyDB / employees / employee [
dependents ] return
3 $ e / lname
7
Having that: Low Income Group (earning to 40000), Middle-Income-Group
(From 40000 to 60000) and High Income Group (earning more than 80000).
Get last names of employees from Milwaukee along with their income group:
1 {}
2 < IncomeGroup >
3 {
4 let $ d: = doc (” company .xml”) for
5 $ e in
6 $ d / companyDB / employees / employee [ contains ( address ,
” Milwaukee “)]
7 return < emp >{$ e / lname }
8 < income >
9 {if ($ e/ salary >= 80000) then ” High Income ”
10 else if ($ e / salary >=60000) then ” Middle Income ”
11 else “Low Income ”
12 }
13 </ income >
14 </ emp >
15 }
16 </ IncomeGroup >
Get employee names of employees who work on all projects located in “Houston”.
1 let $ d: = doc (” company .xml”)
2 let $ houstonProjs : =
3 $ d / companyDB / projects / project [ plocation =” Houston ”
]
4 for $ e in $ d / companyDB / employees / employee where
5 every $ p in $ houstonProjs satisfies ( some $q in
6 $ e / projects / worksOn satisfies
7 $ p / @pnumber = $ q / @pno )
8 return concat ($ e/ fname ,”, ” ,$ e / lname )
8