5. XML Query Languages I 5.1 Introduction 5.1 Introduction 5.1

25.11.2009
5. XML Query Languages I
5.1 Introduction
5.2 XPath
XML Databases
5.3 SQL/XML queries
5. XML Query Languages, 25.11.09
5.4 Overview
Silke Eckstein
Andreas Kupfer
Institut für Informationssysteme
Technische Universität Braunschweig
http://www.ifis.cs.tu-bs.de
5.5 References
2
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.1 Introduction
5.1 Introduction
• Querying XML Data
• Querying XML Data (ctd.)
– "Querying XML data" essentially means
•
•
•
•
– XQuery embedded in SQL statements
as offered by SQL/XML provides the
broadest functionality.
to identify (or address) nodes,
to test certain further properties of these nodes,
then to operate on the matches,
and finally, to construct result XML documents as answers.
• This approach allows to
– join XML columns,
– combine and join XML with relational data,
– construct XML results as answers to SQL queries.
– In the XML context, the language XQuery plays the role that
SQL has in relational databases.
– XQuery can express all of the above constituents of XML
querying:
• XPath, as an embedded sublanguage, expresses the locate and test
parts;
• XQuery can then iterate over selected parts, operate on and construct
answers from these.
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
3
[NK09]
5.1 Introduction
5.1 Introduction
Characteristics of XML
Query Options in DB2
SQL/XML
ISO/IEC 9075-14:SQL/XML
XML predicates
XQUERY 1.0
Expressions
XPATH 2.0
http://www.w3.org/TR/xquery
http://www.w3.org/TR/xpath20
Plain
SQL
SQL/XML with
Plain XQuery XQuery with
XPath or XQuery
embedded SQL
-
++
++
++
++
++
-
+
Parameter markers for XML
predicates
-
++
-
-
Joining XML and relational
-
++
-
++
Joining XML with XML
-
++
++
++
Suitability for XML-only apps.
-
+
++
+
Realtional predicates
Functions & Operators
Insert, update, delete
++
++
-
-
http://www.w3.org/TR/xquery-operators/
Transforming XML data
-
+
++
++
Full-text search
+
++
+
+
Aggregation and grouping
-
++
+
+
++
++
-
++
XQuery 1.0 and XPath 2.0 Data Model
http://www.w3.org/TR/xpath-datamodel/
User-defined functions
[NK09]
4
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5
[NK09]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
6
1
25.11.2009
5.1 Introduction
5.1 Introduction
• XPath as an embedded sublanguage
• XPath – Navigational access to XML documents
– XPath is a declarative, expression-based language to locate
and test doc nodes (with lots of syntactic sugar to make
querying sufficiently sweet).
– Addressing document nodes is a core task in the XML world.
XPath occurs as an embedded sub-language in
– In a sense, the traversal or navigation of trees of XML nodes
lies at the core of every XML query language.
– To this end, XQuery embeds XPath as its tree navigation
sub-language:
• Every XPath expression also is a correct XQuery expression.
• XPath 2.0: http://www.w3.org/TR/xpath20/ .
• XSLT (extract and transform XML document [fragments] into XML,
XHTML, PDF, . . . )
• XQuery (compute with XML document nodes and contents, compute
new docs, . . . )
• XPointer (representation of the address of one or more doc nodes in
a given XML document)
• XMLSchema (represent sets of elements as scopes for uniqueness or
key concepts)
– Since navigation expressions extract (potentially huge volumes
of) nodes from input XML documents, the efficient
implementation of the sub-language XPath
is a prime concern when it comes to the
construction of XQuery processors.
7
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.1 Introduction
5.1 Introduction
• XML Pointer Language
<City>
Storing XML
documents inside
the database as
values of type XML
• XML Linking Language
– To define links, which can…
<City>
<Name>
Braunschweig
</Name>
<Zip>38100</Zip>
<Zip>38106</Zip>
<State>
Niedersachsen
</State>
</City>
Generating XML
documents using
SQL/XML functions
– Datatype XML with
Mapping between SQl and XML
belonging functions
SQL
XQuery
– Mapping between
SQL and XML
SQL database
XML datatype
– Embedding XQuery in SQL
• Be as easy as HTML links
• Have multiple end points
• Be stored independently of the referenced documents
– Allows to add metadata to links
– Uses XPointer and XPath
9
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.1 Introduction
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
10
5. XML Query Languages I
• XML/SQL
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
<City>
• SQL/XML
– Allows to use XPath expressions in URIs
– Used to identify ranges within XML documents
– XML data type
– Type conversion with XMLSERIALIZE,
XMLPARSE, XMLCAST
– Schema validation with XMLVALIDATE
– XML publishing functions XMLELEMENT,
XMLATTRIBUTE, XMLAGG
– Functions & predicates to embed XPath and
XQuery in SQL statements: XMLQUERY,
XMLTABLE and XMLEXISTS
8
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.1 Introduction
(chap. 10)
5.2 XPath
(chap. 10)
(chap. 10)
5.3 SQL/XML
(chap. 9)
5.5 References
5.4 Overview
(here)
11
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
12
2
25.11.2009
5.2 XPath
5.2 XPath
• Multiple steps
• Context node
– An XPath navigation may consist of multiple steps
stepi ,i ≥ 1 taken in succession.
– In XPath, a path traversal starts off from a sequence
of context nodes.
– Step step1 starts o from the context node sequence cs0 and
arrives at a sequence of new nodes cs1.
– cs1 is then used as the new context node sequence for
step2, and so on.
• XPath navigation syntax is simple:
An XPath step
cs0/step
• cs0 denotes the context node sequence, from
which a navigation in direction step is taken.
Multi-step XPath path
cs0/step1/step2/…
≡
• It is a common error in XQuery expressions to try and
start an XPath traversal without the context node sequence
being actually defined.
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
13
((cs0/step1)/step2)/…
cs1
[Scholl07]
5.2 XPath
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
14
5.2 XPath
• XPath location steps
• XPath axes
– XPath defines a family of 12 axes allowing for flexible
navigation within the node hierarchy of an XML tree.
– XPath axes semantics
XPath step
ax :: nt [p1] … [pn]
– A step (or location step) stepi specifies
• ○ marks the context node
• @ marks attribute nodes,
• ● represents any other node
kind (inner ● nodes are
element nodes).
1. the axis ax, i.e., the direction of navigation
taken from the context nodes,
2. a node test nt, which can be used to navigate to nodes of
certain kind (e.g., only attribute nodes) or name,
3. optional predicates pi which further filter the sequence
of nodes we navigated to.
@
@
@
@
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
15
[Scholl07]
5.2 XPath
16
5.2 XPath
• XPath axes:
child, parent, attribute
• XPath axes:
descendant, ancestor, self
– The child axis does not navigate
to the attribute nodes below ○.
– The only way to access
attributes is to use the
attribute axis explicitly.
@
– In a sense, descendant and
ancestor represent the transitive
closures of child and parent,
respectively.
@
@
@
@
@
@
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
@
17
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
18
3
25.11.2009
5.2 XPath
5.2 XPath
• XPath axes:
preceding, following, ancestor-or-self
• XPath axes:
preceding-sibling, following-sibling,
descendant-or-self
– Note: In the serialized XML document,
nodes in the preceding (following)
axis appear completely
before (after) the
context node.
@
@
@
@
@
@
@
[Scholl07]
@
19
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Scholl07]
5.2 XPath
5.2 XPath
• XPath axes: Examples (1)
• XPath axes: Examples (2)
– In these first examples, there is a single initial context
node, i.e., a context node sequence of length 1: the
root element a.
XPath example
(<a b="0">
<c d="1"><e>f</e></c>
<g><h/></g>
</a>)/attribute::node()
• Here, we set the node test nt to simply node() which
means to not filter any nodes selected by the axis.
XPath example
(<a b="0">
<c d="1"><e>f</e></c>
<g><h/></g>
</a>)/child::node()
[Scholl07]
⇒
XPath example
(<a b="0">
<c d="1"><e>f</e></c>
<g><h/></g>
</a>)/descendant::node()
(<c d="1">
<e>f</e>
</c>,
<g><h/></g>)
21
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Scholl07]
5.2 XPath
XPath example
(<a b="0">
<c d="1"><e>f</e></c>
<g><h/></g>
</a>)/child::node()/child::node()
⇒
attribute b { "0" }
⇒
(<c d="1"><e>f</e></c>,
<e>f</e>,
text { "f" },
<g><h/></g>),
<h/>
)
22
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.2 XPath
• XPath axes: Examples (3)
• XPath results: Order & duplicates
⇒
– XPath Semantics: The result node sequence of
any XPath navigation is returned in document
order with no duplicate nodes (remember: node identity).
– Examples:
(<e>f</e>,
<h/>
)
Duplicate nodes are removed in XPath results . . .
(<a b="0">
<a>
<c d="1"><e>f</e></c>
...
⇒
<g><h/></g>
</a>
</a>)/child::node()/parent::node()
– Notes:
• If an extracted node has no suitable XML representation by itself,
XQuery serializes the result using the XQuery node constructor
syntax, e.g.,
attribute b {"0" } or text { "f" } .
• Nodes are serialized showing their content. This does not imply
that all of the content nodes have been selected by the XPath
expression!
[Scholl07]
20
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
23
(<a><b/><c/><d/>
</a>
)/child::node()/following-sibling::node()
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
⇒
(<c/>,
<d/>
)
24
4
25.11.2009
5.2 XPath
5.2 XPath
• XPath: Node test
• XPath: Results in document order
XPath: context node sequence of length > 1
(<a><b/><c/></a>,
⇒
<d><e/><f/></d>)/child::node()
– Once an XPath step arrives at a sequence of nodes, we may
apply a node test to filter nodes based on kind and name.
(<b/>,<c/>,
<e/>,<f/>)
– Note:
• The XPath document order semantics require <b/> to
occur before <c/> and <e/> to occur before <f/>.
– The result (<e/>,<f/>,<b/>,<c/>) would have been OK
as well.
– In contrast, the result (<b/>,<e/>,<c/>,<f/>) is
inconsistent with respect to the order of nodes from
separate trees!
[Scholl07]
text()
preserve text nodes only
attribute()
preserve attribute nodes only
comment()
preserve comment nodes only
[Scholl07]
• XPath: Node test example
Collect and concatenate all text nodes of a tree
string-join
(<a><b>A<c>B</c></b>
<d>C</d>
</a>/descendant-or-self::node()/child::text()
, "")
– A node test may also be a name test, preserving only those
element or attribute nodes with matching names.
Name Test
Semantics
name
preserve element nodes with tag name only
(for attribute axis: preserve attributes)
*
preserve element nodes with arbitrary tag names
(for attribute axis: preserve attributes)
– The XQuery builtin function string-join has signature
string-join(string*, string) as string .
– Note:
• In general we will have cs/ax::* ⊆ cs/ax::node().
Equivalent: compute the string value of node a
string(<a><b>A<c>B</c></b>
"ABC"
⇒
<d>C</d>
</a>)
27
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[Scholl07]
5.2 XPath
• XPath: Predicates
– The strict XPath requirement to construct a result in
document order may imply sorting effort depending on the
actual XPath implementation strategy used by the processor.
(<y id="0"/>,
<y id="1"/>)
– The optional third component of a step formulates a
list of predicates [p1]...[pn] against the nodes selected
by an axis.
– XPath predicate evaluation:
• Predicates have higher precedence than the XPath step
operator /,i.e.:
cs/step[p ][p ] ≡ cs/((step [p ])[p ])
• The pi are evaluated left-to-right for each node in turn. In pi,
the current context item is available as '.'.
– Context item: predicates may be applied to sequences of
arbitrary items (not only nodes)
1
• In many implementations, the descendant-or-self::x step will
yield the context node sequence (<x>...</x>,<x>...</x> ) for
the child::y step.
• Such implementations thus will typically extract <y id="1"/> before
<y id="0"/> from the input document.
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
28
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.2 XPath
• XPath: Ensuring order is not for free
[Scholl07]
26
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.2 XPath
• XPath: Name test
(<x>
<x><y id="0"/></x>
<y id="1"/>
⇒
</x>
)/descendant-or-self::x/child::y
Semantics
preserve processing instructions
processing-instruction(p) preserve processing instructions
of the form <?p ...?>
document-node()
preserve the (invisible)
document root node
5.2 XPath
[Scholl07]
let any node pass
processing-instruction()
25
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
Kind Test
node()
29
[Scholl07]
2
1
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
2
30
5
25.11.2009
5.2 XPath
5.2 XPath
• XPath: Predicates
• XPath: Predicate example
– An XPath predicate pi, may be any XQuery expression evaluating
to some value v. To finally evaluate the predicate, XQuery computes the
effective Boolean value ebv(v).
Value v
ebv(v)
()
false()
0, NaN
false()
""
false()
false()
false()
x
true()
(x1, x2,..., xn)
true()
Select all elements with an id attribute
(<a id="0">
<b><c id="1"/></b>
<b><c><b/></c></b>
⇒
<d id="2">e</d>
</a>
)/descendant-or-self::*[./attribute::id]
Select all elements with a "b" grandchild element
• Item x ∉ {0,""; NaN; false()}, items xi arbitrary. Builtin function
boolean(item*) as boolean also computes the effective Boolean
value.
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
31
(<a id="0">
<b><c id="1"/></b>
<b><c><b/></c></b>
⇒
<d id="2">e</d>
</a>
)/descendant-or-self::*[./child::*/child::b]
• Atomization
– Atomization turns a sequence (x1,...,xn) of
items into a sequence of atomic values (v1,...,vn):
– In XQuery, if any item x – atomic value or node – is used in a
context where a value is required, atomization is applied to
convert x into an atomic value.
1. If xi is an atomic value, vi ≡ xi
2. if xi is a node, vi is the typed value of xi .
– Please note: the typed value is equal to the string
value if xi has not been validated. In this case, vi has
type untypedAtomic.
• Nodes in value contexts commonly appear in XPath predicates.
Consider:
Value comparison in a predicate
(<b>42</b>,
<c><d>42</d></c>,
<d>42</d>
)
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
33
– The XQuery builtin function
data(item*) as anyAtomicType*
may be used to perform atomization explicitly (rarely
necessary).
[Scholl07]
5.2 XPath
• Atomization and subtree traversals
– Since atomization of nodes is pervasive in XQuery
expression evaluation, e.g., during evaluation of
–
Atomization
(and casting) made explizit
(<a>
<b>42</b>
<c><d>42</d></c>
<e>43</e>
</a>)/descendant-or-self::*[data(.) cast as double
eq
42 cast as double]
• arithmetic and comparison expressions,
• function call and return,
• explicit sorting (order by),
– efficient subtree traversals are of prime importance for
any implementation of the language:
Applying data() to a node and its subtree
data(<a>
b
<b>fo</b>o<c>
<d>b</d><e>ar</e>
≡ data
</c>
"fo"
</a>)
– Note: the value comparison operator eq is witness to the
value context in which '.' is used in this query.
– For the context item <c><d>42</d></c> (a nonvalidated node), data(.) returns "42" of type
untypedAtomic.
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
34
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.2 XPath
• XPath: Predicates and atomization
[Scholl07]
32
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.2 XPath
• XPath: Predicates and atomization
[Scholl07]
<b>
<c><b/></c>
</b>
– Note: Existential semantics of path predicates.
[Scholl07]
5.2 XPath
(<a>
<b>42</b>
<c><d>42</d></c>
⇒
<e>43</e>
</a>)/descendant-or-self::*[. eq 42]
(<a id="0">
...
</a>,
<c id="1"/>,
<d id="2">e</d>
)
35
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
a
c
"o" d
"b"
e
"ar"
36
6
25.11.2009
5.2 XPath
5.2 XPath
• XPath:The context item '.'
• XPath: Positional access
– Inside a predicate [p] the current context item is '.'.
• An expression may also access the position of '.' in the context
sequence via position(). The first item is located at position 1.
• Furthermore, the position of the last context item is available via
last().
Positional access
(x1,x2,...,xn) [position() eq i] ⇒ xi
(x1,x2,...,xn)[position() eq last()] ⇒ xn
– As a useful generalization, XPath makes the current
context item '.' available in each step (not only in
predicates).
– In the expression
cs/e
expression e will be evaluated with '.' set to each item
in the context sequence cs (in order). The resulting
sequence is returned.
• Remember: if e returns nodes (e has type node*), the
resulting sequence is sorted in document order with
duplicates removed.
• A predicate of the form [position() eq i] with i being any
XQuery expression of numeric type, may be abbreviated by [i].
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
37
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.2 XPath
5.2 XPath
• Combining node sequences
Accessing '.'
– Sequences of nodes (e.g., the results of XPath location step)
may be combined via
(<a>1</a>,<b>2</b>,<c>3</c>)/(. + 42)
⇒(43.0,44.0,45.0)
• |, union (used synonymously), intersect, except
– These operators remove duplicate nodes based on identity
and return their result in document order.
– Note: Introduced in the XPath context because a number of
useful navigation idioms are based on these operators:
(<a>1</a>,<b>2</b>,<c>3</c>)/name(.)
⇒ ("a","b","c")
Selecting all x children and attributes of context node
cs/(./child::x | ./attribute::x)
(<a>1</a>,<b>2</b>,<c>3</c>)/position()
⇒ (1,2,3)
Select all siblings of context node
cs/(./preceding-sibling::node()
| ./following sibling::node())
or
cs/(./parent::node()/child::node() except .)
(<a><b/></a>)/(./child::b, .)
⇒ (<a><b/></a>,<b/>)
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
39
[Scholl07]
5.2 XPath
Abbreviation
Expansion
a/b/c
./child::a/child::b/child::c
a//@id
./child::a/descendant-or-self::node()/attribute::id
Abbreviation
Expansion
//a
root(.)/descendant-or-self::node()/child::a
nt
child::nt
a/text()
./child::a/child::text()
@
attribute::
..
parent::node()
//
/descendant-or-self::node()/
/1
root(.)/
1(At
40
• XPath abbrevation examples
– Since XPath expressions are pervasive in XQuery, query
authors commonly use the succinct abbreviated XPath
syntax to specify location steps.
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.2 XPath
• XPath: Abbreviations
step1
38
./step
the beginning of a path expression.)
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
41
[Scholl07]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
42
7
25.11.2009
5. XML Query Languages I
5.3 SQL/XML Queries
• Motivation
5.1 Introduction
– How can SQL applications
locate and retrieve information
in XML documents stored in an SQL database cell?
– Invoking XML query language within SQL statements
5.2 XPath
5.3 SQL/XML
5.4 Overview
• Retrieve information — in SELECT list
• Locate information — in WHERE clause
5.5 References
– Details on XML query language XQuery later
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
43
[Mel05]
5.3 SQL/XML Queries
• XMLQuery
– A scalar function that is typically used in the SELECT
clause of an SQL query to extract XML fragments or
values from an XML document
– Storing XML in relational databases is possible as
• Character data (VARCHAR, Character Large OBject)
• New data type XML
• XMLTABLE
– A table function that is used in the FROM clause of an
SQL statement. It reads one or multiple values from an
XML document and returns them as a set of rows.
– A value of the data type XML can contain
• whole XML document
• XML element
• a set of XML elements
• XMLExists
– A predicate that is commonly used in the WHERE clause of an
SQL statement to express predicates over XML data.
– All XML publishing operators (cf. lecture 9) create
values of the data type XML, not a string
– More on this later (cf. lecture 10)
• XMLCAST
– A function that converts individual XML values to SQL data
types.
45
[NK09]
5.3 SQL/XML Queries
46
• Retrieving XML documents
SELECT info
FROM customer
WHERE id = 1003;
<customerinfo Cid ="1003">
<name>Robert Shoemaker</name>
<addr country = "Canada">
<street>845 Kean Street</street>
<city>Aurora</city>
<prov-state>Ontario</prov-state>
<pcode-zip>N8X 7F8</pcode-zip>
</addr>
<phone type="work">905-555-7258</phone>
<phone type="home">416-555-2937</phone>
</customerinfo>
SELECT XMLQUERY ('$i/customerinfo/name' passing info as "i")
FROM customer;
--in short:
SELECT XMLQUERY ('$INFO/customerinfo/name')
FROM customer;
--Output:
<customerinfo Cid ="1004">
...
</customerinfo>
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
5.3 SQL/XML Queries
CREATE TABLE customer (id INTEGER, info XML);
[NK09]
44
5.3 SQL/XML Queries
• XML data type
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
<name>Robert Shoemaker</name>
<name>...</name>
47
[NK09]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
48
8
25.11.2009
5.3 SQL/XML Queries
5.3 SQL/XML Queries
• XMLTABLE
• XMLQuery syntax
– Provides an SQL view of XML data
XMLQUERY(<XQuery expression>
[PASSING <argument list>]
{NULL | EMPTY} ON EMPTY)
•
argument list := <SQL value> AS <XQuery variable>
• Output is not of the XML type
– Evaluates an XQuery “row pattern” with
optional arguments (as with XMLQuery)
– Element/attribute values mapped to columns using XQuery
“column patterns”
– Names & types of columns required; default values optional
– Syntax:
– Example
SELECT
XMLQUERY(
•
'<State name="{$Name}"><City>{$City}</City></State>'
PASSING State as $Name, City AS $City NULL ON EMPTY)
AS CityList FROM Cities;
XMLTABLE (<XQuery expression>
PASSING <argument list>
COLUMNS <column list>)
CityList
<State name="Niedersachsen"><City>Braunschweig</City></State>
column := <name> <type> PATH <path expression>
<State name="Niedersachsen"><City>Hannover</City></State>
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
49
[Mel05]
5.3 SQL/XML Queries
• XMLEXISTS
SELECT XMLQUERY(
'$i/customerinfo/name' PASSING info AS "i")
FROM customer
WHERE XMLEXISTS('$i/customerinfo[addr/city = "Aurora"]');
SELECT T.*
FROM customer,
XMLTABLE('$INFO/customerinfo'
COLUMNS
custID
INTEGER
PATH '@cid',
custname VARCHAR(20) PATH 'name',
street
VARCHAR(20) PATH 'addr/street',
city
VARCHAR(16) PATH 'addr/city'
) AS T;
custID
custname
street
1003
Robert Shoemaker
8845 Kean Street
Aurora
1004
Matt Foreman
1596 Baseline
Toronto
<name>Robert Shoemaker</name>
1 record(s) selected.
city
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
51
5.3 SQL/XML Queries
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
Introduction and Basics
1. Introduction
2. XML Basics
3. Schema Definition
4. XML Processing
Querying XML
5. XPath & SQL/XML
Queries
6. XQuery Data Model
7. XQuery
XML Updates
8. XML Updates & XSLT
SELECT id, info
FROM customer
ORDER BY
XMLCAST(
XMLQUERY('$i/customerinfo/name' PASSING info AS "i")
AS VARCHAR(25));
--alternative:
SELECT id, info
FROM customer,
XMLTABLE('$i/customerinfo/name' PASSING info AS "i"
COLUMNS
custname VARCHAR(25) PATH '.')
ORDER BY custname;
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
[NK09]
52
5.4 Overview
• Ordering a query result set based on converted
XML values
[NK09]
50
5.3 SQL/XML Queries
• XMLTable: Example
[Tür08]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
53
Producing XML
9. Mapping relational data
to XML
Storing XML
10. XML storage
11. Relational XML storage
12. Storage Optimization
Systems
13. Technology Overview
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
54
9
25.11.2009
5.5 References
5.5 References
• http://www.w3.org/ [W3C]
• XML in a Nutshell [HM04]
– XPath, XPointer, XLink, XQuery, XSLT
– Harold & Means
– O'Reilly, 2004, ISBN 0596007647
• M. Scholl, "XML and Databases", Lecture, Uni
Konstanz, WS07/08 [Scholl07]
• XML und Datenmodellierung [EE04]
– R. and S. Eckstein
– Dpunkt-Verlag, 2004, ISBN 3898642224
• DB2 pureXML CookBook [NK09]
– Matthias Nicola and Pav Kumar-Chatterjee
– IBMPress, 2009, ISBN 9780138150471
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
55
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
56
Questions, Ideas, Comments
• Now, or ...
• Room:
IZ 232
• Office our:
Tuesday, 12:30 – 13:30 Uhr
or on appointment
• Email:
[email protected]
XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig
57
10