Unformatted Attachment Preview
Data base xml XPath qustions:
1- Create a procedure that returns name of emp whose salary is over a parameter?
2- Use . to return the salary element of emplouees whose salary is over 50000.
3- 3-Please return the names of employees using extract function and xpathe. Please do not
return tags.
Querying XML Data (1)
1
Roadmap
XPath and Oracle XPath support
Overview
Tree model of XML
Basic XPath structure
Using attributes
Wild card and position matching
Predicates
2
Querying and Transforming
XML Data
XPath
XSLT
Simple language consisting of path expressions
Simple language designed for translation from XML
to XML (in another schema) and XML to HTML
XQuery
An XML query language with a rich set of
features
3
Tree Model of XML Data
An XML document is modeled as a tree, with nodes
corresponding to elements and attributes
Element nodes have children nodes, which can be attributes or
subelements
Text in an element is modeled as a text node child of the
element
bank2
account
Downtown
Account- branch500
number
name
A-401
Downtown
balance
500
4
Exercise
Suppose you have created the following table:
create table emp_xml(id int primary key, xml_column xmltype);
insert into emp_xml values
(1, xmltype('
jeff
IT
2005-1-1
70000
susan
HR
2005-6-1
50000
'));
5
Exercise
Please draw this XML document in tree model
6
Roadmap
XPath and Oracle XPath support
Overview
Tree model of XML
Basic XPath structure
Using attributes
Wild card and position matching
Predicates
7
XPath
XPath is used to address (select) parts of documents using
path expressions
A path expression is a sequence of steps separated by “/”
Similar to file directories
Always start from root
Each step is a node name (element or attribute)
It can also have predicates (later)
Result of path expression: set of values that along with their
containing elements/attributes match the specified path
8
XPath (Cont.)
The initial “/” denotes root of the document (above the top-level tag)
Path expressions are evaluated left to right
Each step operates on the set of instances produced by the previous step
Return the last element in the path (including tags)
9
XPath (Cont.)
E.g.
/bank/account/branch-name
Results?
bank
account
Account- branchDowntown
number
name
A-401
Downtown
500
Uptown
100
account
balance
…
500
10
XPath (Cont.)
/bank/account/branch-name
Downtown
Uptown
bank
Downtown
account
500
Account- branch
number
name
Uptown
A-401
Downtown
100
account
balance
…
500
11
How to execute XPath in Oracle?
Extract fractions of documents satisfying XPath
extract(xpath) function
Return a xmltype data
Template:
Select alias-name.xml-column-name.extract(xpath)
From table-name alias-name
Alias is necessary for calling extract function
(need to use table alias in oracle when there are two dots)
12
How to execute XPath in
Oracle?
For long results
Select alias-name.xml-columnname.extract(xpath).getstringval()
From table-name alias-name
13
Example
CREATE TABLE example1
(key_column VARCHAR2(10) primary key,
xml_column XMLType);
INSERT INTO example1
VALUES
('bank2',
xmltype('
Downtown
500
Uptown
100
'));
14
Example
Select x.xml_column.extract('/bank/account/branch-name')
from example1 x;
15
XPath (Cont.)
Text() function returns text content only (no tags)
select x.xml_column.extract('/bank/account/branch-name/text()')
from example1 x;
Results?
Downtown
Downtown
500
Uptown
100
16
Exercise
Suppose you have created the following table:
create table emp_xml(id int primary key, xml_column xmltype);
insert into emp_xml values
(1, xmltype('
jeff
IT
2005-1-1
70000
susan
HR
2005-6-1
50000
'));
17
Exercise (cont.)
Please return the names of emp using extract
function and xpath
18
Exercise (cont.)
Please return the names of employees using
extract function and xpath. Please do not
return tags.
19
Common Error
Misspell element names (wrong xpath)
Forget to use table alias when calling
extract function
Forget to put xpath in quote
String values in xpath shall be doublequoted
20
Roadmap
XPath and Oracle XPath support
Overview
Tree model of XML
Basic XPath structure
Using attributes
Wild card and position matching
Predicates
21
Attributes
Attributes are accessed using “@”
E.g. return account-number of account
/bank/account/@account-number
Results: A-401 A-501
Note that tags for attributes are not returned
Downtown
500
Downtown
100
22
In Oracle
select x.xml_column.extract(
'/bank/account/@account-number')
from example1 x;
23
Exercise
Return the eid of emp in emp_xml table
24
Roadmap
XPath and Oracle XPath support
Overview
Tree model of XML
Basic XPath structure
Using attributes
Wild card and position matching
Predicates
25
Return Descendants
“//” can be used to skip multiple levels of
nodes
E.g. /bank//branch-name returns any branchname descendants under bank
E.g. //branch-name returns any branch-name
elements in the current XML document
Useful when you do not want to remember
all the intermediate levels
26
Return Descendants
Downtown
500
Uptown
100
Results of /bank//branch-name :
Downtown
Uptown
27
In Oracle
select x.xml_column.extract(
'/bank//branch-name').getstringval()
from example1 x;
28
Exercise
Use // to return salary elements from
emp_xml table
29
Matching Positions
Return the first account in the bank xml
document
select x.xml_column.extract(
'/bank/account[1]').getstringval()
from example1 x;
30
Matching Positions (cont.)
Return the second account in the bank
xml document
select x.xml_column.extract(
'/bank/account[2]').getstringval()
from example1 x;
31
Matching Positions (cont.)
Return the last account in the bank xml
document
select x.xml_column.extract(
'/bank/account[last()]').getstringval()
from example1 x;
last() function returns the last node
32
Matching Positions (cont.)
Return the first two accounts in the bank
xml document
select x.xml_column.extract(
'/bank/account[position() 400]').getstringval()
from example1 x;
38
Prediates
Return the branch-name of those accounts with balance over 400.
/bank/account[balance > 400]/branch-name
[balance > 400] restricts the element right before it (account)
Result: Downtown (of the first
account)
Downtown
500
Uptown
100
39
Common Errors
Where to put []: right after the parent element of
the predicate
E.g. Wrong:
/bank/account/balance[balance > 400]/branch-name
Correct:/bank/account[balance > 400]/branch-name
No need to add / in the beginning of [] ([] is the
child node by default)
Wrong: /bank/account[/balance > 400]/branch-name
Correct: /bank/account[balance > 400]/branch-name
40
Exercise
Use the emp_xml table you have
created
Please return the salary of employee
“jeff”
41
Exercise (cont.)
Please return the name of employee
whose salary is over 60000
42
Allowed Operators in
Predicates
=, !=, >, =, 400]')
from example1 x;
20
Exercise
Use . to return the salary element of
employees whose salary is over 50000.
21
How to Return One XML Node
Per Row
Using Extract may return more than one node per
row
e.g. select
x.xml_column.extract('/bank/account/balance')
from example1 x;
Results:
500 100
1500 900
500 100
22
Solution
select value(t)
from example1 x,
table(xmlsequence(
extract(x.xml_column,'/bank/account/balance'))) t
The extract function returns multiple nodes per row
500 100
1500 900
500 100
23
Solution
select value(t)
from example1 x,
table(xmlsequence(
extract(x.xml_column,'/bank/account/balance'))) t
The xmlsequence function makes sure each row has one node
500
100
1500
900
500
100
24
Solution
select value(t)
from example1 x,
table(xmlsequence(
extract(x.xml_column,'/bank/account/balance'))) t
The table function makes these rows into a table t
500
100
1500
900
500
100
25
Solution
select value(t)
from example1 x,
table(xmlsequence(
extract(x.xml_column,'/bank/account/balance'))) t
The value(t) in select returns nodes in the table t
We can not write select t directly because t is a table
500
100
1500
900
500
100
26
Exercise
Return the salary from emp_xml table,
make sure one row has only one node
27
Convert XMLType to Scalar Value
Return balance of each account as a number
(not a xmltype node)
select extractvalue(value(t),'/')
from example1 x,
table(xmlsequence(
extract(x.xml_column,'/bank/account/balance'))) t
First makes sure one node per row, then use
extractvalue function
28
Syntax of ExtractValue
Extractvalue(xmltype node, xpath)
select extractvalue(value(t),'/')
from example1 x,
table(xmlsequence(
extract(x.xml_column,'/bank/account/balance'
))) t
29
Example
Compute average balance
select avg(to_number((extractvalue(value(t),'/‘)))
from example1
x,table(xmlsequence(extract(x.xml_column,'/bank/a
ccount/balance'))) t
30
Example
How about?
select avg(value(t))
from example1
x,table(xmlsequence(extract(x.xml_colu
mn,'/bank/account/balance'))) t
31
Exercise
Compute highest salary in emp_xml
table
32