XML Query Languages

1. XML Query Languages Database Systems (4th edition) Chapter 30.5, 30.3.3, 30.3.4 Articles & Excerpts XML Query Languages * 2 XQuery Computer Environment Tutorials DB2 & XML Querying XML Data with XQuery

2. XML Query Languages Languages for querying data stored inside XML structures • XPath • XSLT • XQuery • XML/SQL (SQL 2003c) • Proprietary languages (IBM, Oracle, etc.) • …

3. XPath • Path expressions for navigating through XML structures • Possible to set conditions and use wildcards • Also includes many functions • Example: • /Book[@Price=500]/Author/@Name

4. XSLT • Enables transformations between different XML structures (mostly used for XML to HTML transformations) • Example: <xsl:template match=“/”> <authors> <xsl:for-each select=“books/book/author”> <author><xsl:value-of select=“name”></author> </xsl:for-each> </authors> </xsl:template> <books> <book title=“x”> <author name=“a”/> </book> <book title=“y”> <author name=“b”/> </book> </books> <authors> <author>a</author> <author>b</author> </authors>

5. XQuery • Query language for XML • Combines XPath and FLWOR expressions • FLWOR: For Let Where Order by Return • Supports use of all the functions included in XPath • Inspired by many other languages like SQL, OQL, Lorel, etc.

6. XML/SQL • SQL 2003 (ISO standard) • Support for XML in “relational” databases • Storage • Querying through XQuery • Support for constructing XML from relational data with SQL

7. Proprietary Languages • Support for XML data and transformations between XML and relational data. • IBM: • SQL UDFs: Extract-functions, Update-function • Mapping schemes: DAD-files • Oracle: • SQL UDFs: Extract-functions, existsNode, etc. • Others

8. Example <Test Place="Kista" Deadline="2002.09.21"> <Description version="2.1"> This test is about the effects of computer games on the human brain </Description> <Phase Status="finished" Index="2" Date="2001.03.11"> <Details>Choose the computer games to be used for the test</Details> <Results>Age of Empires</Results> <Results>Flight Simulator</Results> <Results>Tetris</Results> </Phase> <Phase Status="started" Index="3"> <Details>Let users try the computer games</Details> <Results> Many users find Flight Simulator hard due to 3D environment and multiple controls </Results> </Phase> <Phase Status="cancelled" Index="1" Date="2001.01.09"> <Details>Try to get funding from EU</Details> </Phase> <Phase Status="not-started" Index="4"> <Details>Present the results of the test</Details> </Phase> </Test>

9. XPath • / • // • @ • /element/@attribute • /elementX//elementY • Wildcards * nodes() • Predicates: [predicate]: • /element[1] • /element[@attribute=value] • . and .. (current node and parent node) • | (concatenation)

10. XPath Examples • Any Results node: • //Results • Any Phase that is cancelled • //Phase[@status=‘cancelled’] • The Date of a Phase that has a Results • //Phase/Results/../@Date • The Results of the Phase with Index 2 or 3 • /Test/Phase[@Index=2]/Results | /Test/Phase[@Index=3]/Results

11. XPath Axes • child, ancestor, descendant, parent • /Test/child::Phase (equivalent to /Test/Phase) • //Results/ancestor::Test

12. XQuery • For • Loop through a list/set of nodes/values • Let • Assignments • Where • Conditions • Order By • Sort the result • Return • Construct an output structure

13. XQuery • FLWOR expressions can be nested. • No clause is compulsory. • XPath expressions can be used in any clause • The result can be a valid XML structure, but it doesn’t have to be. • The function doc() can be used to define the input (XML source), or the execution environment can define an input context.

14. XQuery • Variables start with $: • for $a in //Book/Author • let $n := $a/@Name • Sets: • for $x in (1, 2, 3) • let $y := (1, 2, 3) • Evaluating expressions: • Enclose the expression in { }: • <result>{$x*3}</result>

15. XQuery – Multiple results • One result per value in the loop created by the for clause: for $x in (1,2,3) return <value>{$x}</value> • Place the result in a new result: <result>{ for $x in (1,2,3) return <value>{$x}</value> }</result>

16. XQuery/XPath functions • Sequence functions: • distinct-values(s) • min(s), max(s), sum(s), avg(s) • empty(s), exists(s) • union(s1,s2), instersect(s1,s2), except(s1,s2) • concat(s1,s2) • Node functions: • name(n), local-name(n), node-name(n)

17. XQuery/XPath functions • String functions: • matches(s, regexp) • concat(s1,s2) • starts-with(s1,s2), ends-with(s1,s2), contains(s1,s2) • substring(s, start), substring(s, start, length) • lower-case(s), upper-case(s) • replace(s, pattern, replacement) • tokenize(s, pattern)

18. XQuery/XPath functions • Other functions: • doc(URI) • not(e) • several date/time functions • several numeric functions • data(ns) – Sequence of nodes to sequence of atomic values • number(n) – The value of a node as a number or NaN • string(n) – The value of the node as a string • current-time(), current-date(), current-dateTime()

19. XQuery/XPath operators • +, -, *, div, mod • =, !=, >, <, <=, >= • eq, ne, lt, le, gt, ge • or, and, not • is, >>, <<

20. XQuery functions • element() • /element() (similar to /*) • attribute() • /Test/attribute() (similar to /Test/@*) • node() (follows the standard??) • /node() – element nodes and text nodes • /@node() – attribute nodes

21. XQuery – Computed Constructors • element • element name value: let $a := “a”, $b := 2 return <x>{element {$a} {$b}}</x> • attribute • attribute name value: let $a := “a”, $b := 2 return <x>{attribute {$a} {$b}}</x>

22. XQuery – Conditionals • if-then-else for $a in (1 to 5) return if ($a mod 2 = 0) then <even>{$a}</even> else <odd>{$a}</odd>

23. XQuery – Quantifiers • some for $a in /Test where some $b in $a/Phase/@Status satisfies string($b) = "finished" return $a/Description • every for $a in /Test where every $b in $a/Phase/@Status satisfies string($b) = "finished" return $a/Description

24. XQuery – Nesting • One result becomes the source of another expression: for $x in distinct-values (for $a in (1 to 5), $b in (1 to 5) return <sum>{$a + $b}</sum>) return <unique>{$x}</unique>

25. XML/SQL • Functions for generating XML documents as SQL results: • XMLELEMENT • XMLFOREST • XMLATTRIBUTES • XMLAGG • XMLCONCAT

26. Example table structure and data

27. XMLELEMENT • Creates an XML element with a specified name and content: SELECT XMLELEMENT(NAME "Person", name) FROM Person <Person>John Higgins</Person> <Person>Steven Hendry</Person> <Person>Mathew Stevens</Person> <Person>Ken Doherty</Person> <Person>Steve Davis</Person> <Person>Paul Hunter</Person> <Person>Ronnie O'Sullivan</Person> (1 result row per element) DB2: SELECT xml2clob(XMLELEMENT(NAME "Person", name)) FROM Person

28. XMLATTRIBUTES • Creates an XML attributes to be placed inside an XML element: SELECT XMLELEMENT(NAME "Person", XMLATTRIBUTES(yearofbirth), name) FROM Person <Person YEAROFBIRTH="1975">John Higgins</Person> <Person YEAROFBIRTH="1973">Steven Hendry</Person> <Person YEAROFBIRTH="1982">Mathew Stevens</Person> <Person YEAROFBIRTH="1974">Ken Doherty</Person> <Person YEAROFBIRTH="1960">Steve Davis</Person> <Person YEAROFBIRTH="1983">Paul Hunter</Person> <Person YEAROFBIRTH="1980">Ronnie O'Sullivan</Person>

29. XMLATTRIBUTES • SELECT XMLELEMENT(NAME "Person", XMLATTRIBUTES(yearofbirth AS "Year"), name) FROM Person <Person Year="1975">John Higgins</Person> <Person Year="1973">Steven Hendry</Person> <Person Year="1982">Mathew Stevens</Person> <Person Year="1974">Ken Doherty</Person> <Person Year="1960">Steve Davis</Person> <Person Year="1983">Paul Hunter</Person> <Person Year="1980">Ronnie O'Sullivan</Person>

30. XMLATTRIBUTES • SELECT XMLELEMENT(NAME "Person", XMLATTRIBUTES(yearofbirth AS "Year", name)) FROM Person <Person Year="1975" NAME="John Higgins"></Person> <Person Year="1973" NAME="Steven Hendry"></Person> <Person Year="1982" NAME="Mathew Stevens"></Person> <Person Year="1974" NAME="Ken Doherty"></Person> <Person Year="1960" NAME="Steve Davis"></Person> <Person Year="1983" NAME="Paul Hunter"></Person> <Person Year="1980" NAME="Ronnie O'Sullivan"></Person>

31. XMLCONCAT • Combine more elements as a sequence of element. • This would give us two columns: SELECT XMLELEMENT(NAME "Name", name), XMLELEMENT(NAME "Year", yearofbirth) FROM Person • This would give us one column in the result: SELECT XMLCONCAT(XMLELEMENT(NAME "Name", name), XMLELEMENT(NAME "Year", yearofbirth)) FROM Person

32. XMLCONCAT <Name>John Higgins</Name><Year>1975</Year> <Name>Steven Hendry</Name><Year>1973</Year> <Name>Mathew Stevens</Name><Year>1982</Year> <Name>Ken Doherty</Name><Year>1974</Year> <Name>Steve Davis</Name><Year>1960</Year> <Name>Paul Hunter</Name><Year>1983</Year> <Name>Ronnie O'Sullivan</Name><Year>1980</Year> • Still multiple rows though

33. XMLFOREST • Create multiple elements: SELECT XMLFOREST(name AS "Name", yearofbirth AS "Year") FROM Person <Name>John Higgins</Name><Year>1975</Year> <Name>Steven Hendry</Name><Year>1973</Year> <Name>Mathew Stevens</Name><Year>1982</Year> <Name>Ken Doherty</Name><Year>1974</Year> <Name>Steve Davis</Name><Year>1960</Year> <Name>Paul Hunter</Name><Year>1983</Year> <Name>Ronnie O'Sullivan</Name><Year>1980</Year>

34. Combinations • Or make this more complete by combining different functions: SELECT XMLELEMENT(NAME "Person", XMLATTRIBUTES(pid AS "ID"), XMLFOREST(name AS "Name", yearofbirth AS "Year")) FROM Person <Person ID="1"><Name>John Higgins</Name><Year>1975</Year></Person> <Person ID="2"><Name>Steven Hendry</Name><Year>1973</Year></Person> <Person ID="3"><Name>Mathew Stevens</Name><Year>1982</Year></Person> <Person ID="5"><Name>Ken Doherty</Name><Year>1974</Year></Person> <Person ID="6"><Name>Steve Davis</Name><Year>1960</Year></Person> <Person ID="7"><Name>Paul Hunter</Name><Year>1983</Year></Person> <Person ID="4"><Name>Ronnie O'Sullivan</Name><Year>1980</Year></Person>

35. XMLAGG • Grouping many rows into one result. Can be used together with a GROUP BY clause. • Put all the persons in one result row: SELECT XMLAGG(XMLELEMENT(NAME "Person", name)) FROM Person <Person>John Higgins</Person><Person>Steven Hendry</Person><Person>Mathew Stevens</Person><Person>Ken Doherty</Person><Person>Steve Davis</Person><Person>Paul Hunter</Person><Person>Ronnie O'Sullivan</Person> • One result row! (though not well-formed XML)

36. Combinations • Put all the persons in one Persons element: SELECT XMLELEMENT(NAME "Persons", XMLAGG(XMLELEMENT(NAME "Person", name))) FROM Person <Persons><Person>John Higgins</Person><Person>Steven Hendry</Person><Person>Mathew Stevens</Person><Person>Ken Doherty</Person><Person>Steve Davis</Person><Person>Paul Hunter</Person><Person>Ronnie O'Sullivan</Person></Persons> • One result row! (AND well-formed XML)

37. XMLAGG with GROUP BY • One row per group: SELECT XMLELEMENT(NAME "Color", XMLATTRIBUTES(color), XMLAGG(XMLELEMENT(NAME "Car", licencenumber))) FROM Car GROUP BY color <Color COLOR="black"><Car>ABC123</Car><Car>TYD226</Car><Car>RSQ199</Car></Color> <Color COLOR="blue"><Car>CCD457</Car><Car>ROO197</Car></Color> <Color COLOR="green"><Car>DKL998</Car></Color> <Color COLOR="red"><Car>WID387</Car><Car>PTF357</Car></Color>

38. Aggregates without XMLAGG SELECT XMLELEMENT(NAME "Color", XMLATTRIBUTES(color, COUNT(*) as "Amount")) FROM Car GROUP BY color <Color COLOR="black" Amount="3"></Color> <Color COLOR="blue" Amount="2"></Color> <Color COLOR="green" Amount="1"></Color> <Color COLOR="red" Amount="2"></Color>

39. DB2's extra XML support • Store XML documents • Query data inside the XML structures • Change data inside the XML structures • Validate XML documents against DTDs • Decompose XML documents into tables

40. Query XML data • Path expressions • simple version of XPath • extract-functions • two functions for each data type • 1 singleton function, e.g. extractInteger • 1 plural function, e.g. extractIntegers

41. Update XML data • Path expressions • simple version of XPath • update-function • replaces a specified path with a new value • returns the entire XML document

42. More Information • XPath • http://www.w3schools.com/xpath/ • XQuery • http://www.w3schools.com/xquery/ • http://www.stylusstudio.com/xml_tutorial.html • XML/SQL • http://docs.openlinksw.com/virtuoso/composingxmlinsql.html • http://www.stylusstudio.com/sqlxml_tutorial.html