XQUERY

1. XQUERY Aşkın Çelik Volkan Cirik Özgen Çetinkaya

2. What Is XQuery? • XQuery was developedby W3C forqueries on XML data. • XQuery was built on XPath expressions. • As an analogy, XQuery is to XML what SQL is to database tables. • XQuery1.0 wasreleasedon January 23, 2007

3. Why Do WeUseXQuery? • Large number of tools for processing XMLdocuments existtoday. (Lorel, Quilt, UnQL, Xduce, XMLQL, XPath, XQuery, XQL, YaTL). • However W3C recommends XPathand XQuery. • Majordatabasedeveloperslike IBM, Oracle, Microsoft, Software A G supportXQuery.

4. Where Do WeUseIt? • Manipulatedata from XML documents • Extract information to use in a Web Service • Generate summary reports • Transform XML data to XHTML • Search Web documents for relevant information • SomecompanyandprogramsusingXQuery: O’ReillyLabs, New EnglandJournal of Medicine, Business Exchange, Author Mapper, Data Request Broker…

5. Xquery&XpathComparison • XQuery is based on XPathexpressions • XQuery 1.0 andXPath 3.0 sharethesame data model andsupportsamefunctionsandoperators • InXPaththere is nooptiontooperate on theresult data, but in XQuery it is possibletosortortransformresult data to a textbased format like HTML. • XQuery has FLWOR expressions (For, Let, Where, OrderBy, Return)

6. XQueryTerms • XML documents havetreestructure. • The root of the tree is calledthe document node(ortherootnode). • There are 7 kinds of nodes: element, attribute, text, namespace, processinginstruction, comment, and document nodes. • Atomic values are nodes with no children or parent.

7. Example • <?xml version="1.0" encoding="ISO-8859-1"?><bookstore><bookcategory="CHILDREN"><title>Harry Potter</title><author>J K.Rowling</author><year>2005</year><price>29.99</price></book></bookstore> bookstore(documentnode) book(element node) category(attributenode) title(element node) author(element node)year(element node) price(element node) CHILDREN(atomicvalue) Harry Potter(atomicvalue) J K. Rowling(atomicvalue)2005 (atomicvalue)29.95 (atomicvalue) Xml File Nodes

8. Relationship of Nodes • Parent: Each element and attribute has one parent • Children: Element nodes may have zero, one or more children • Siblings: Nodes that have the same parent • Ancestor: A node'sparent, parent'sparent, etc. • Descendent: A node's children, children's children, etc.

9. Example • <?xml version="1.0" encoding="ISO-8859-1"?><bookstore><bookcategory="CHILDREN"><title>Harry Potter</title><author>J K. Rowling</author><year>2005</year><price>29.99</price></book></bookstore> • The book element is the parent of the title, author, year, and price • The title, author, year, and price elements are children of the book element • The title, author, year, and price elements are siblings

10. How To Select Nodes? • Using Functions The doc() function opensthe "books.xml" filebyeither 2 way a) Byrelative URL: doc("books.xml") b) Byabsolute URL: doc("file:///c:/data/xml/books.xml") • Using Path Expressions doc("books.xml")/bookstore/book/title Selectsall the title elements in the "books.xml" file

11. Example • "books.xml": <?xmlversion="1.0" encoding="ISO-8859-1"?><bookstore> <bookcategory="CHILDREN"> <title>Harry Potter</title> <author>J K. Rowling</author> <year>2005</year> <price>29.99</price> </book> <book category="WEB"><title>Learning XML</title><author>Erik T. Ray</author><year>2003</year><price>39.95</price></book></bookstore> doc("books.xml")/bookstore/book/title <title >Harry Potter</title><title >Learning XML</title> books.xml File TheResult

12. How To Select Nodes? • Using Predicates Predicates limitthe extracted data from XML documents: doc("books.xml")/bookstore/book[price<30] It is possibleto limit the data accordingtoattributesbyputting an @ symbol: doc("books.xml")/bookstore/book[@category="WEB"]

13. Example • "books.xml": <?xmlversion="1.0" encoding="ISO-8859-1"?><bookstore> <bookcategory="CHILDREN">  <title>Harry Potter</title>  <author>J K. Rowling</author>  <year>2005</year>  <price>29.99</price> </book> <book category="WEB"><title>Learning XML</title><author>Erik T. Ray</author><year>2003</year><price>39.95</price></book></bookstore> doc("books.xml")/bookstore/book[price<30] <book category="CHILDREN"><title>Harry Potter</title><author>J K.Rowling</author><year>2005</year><price>29.99</price></book> books.xml File TheResult

14. Example • "books.xml": <?xmlversion="1.0" encoding="ISO-8859-1"?><bookstore> <bookcategory="CHILDREN">  <title>Harry Potter</title>  <author>J K. Rowling</author>  <year>2005</year>  <price>29.99</price> </book> <book category="WEB"><title>Learning XML</title><author>Erik T. Ray</author><year>2003</year><price>39.95</price></book></bookstore> doc("books.xml")/bookstore/book[@category="WEB"] <book category="WEB">  <title>Learning XML</title>  <author>Erik T. Ray</author>  <year>2003</year>  <price>39.95</price></book> books.xml File TheResult

15. DoubleSlash • In path expressions,operatorslocate nodes by their position in treehierarchy. Aseries of one or more stepsareusedtoreachthemwhichareseparated by a slash /. • TopreventlongpathexpressionsXQueryprovides double slash//. • doc("books.xml")/bookstore/book/title • The expressionabove contains 3steps. The same could have been doneby the following query, which uses only 1double slash: • doc("books.xml")//title

16. FLWOR • The elements can be selected in XQuery either by using path expressions along with predicates or using FLWOR expression. FLWORis an acronym that stands for: • For - binds a variable to each item returned by the expression • Let - allows variable assignment (optional) • Where - specifies a criteria (optional) • Order by - specifies the sort-order of the result (optional) • Return - specifies what to return in the result

17. Example • for$x in doc("book.xml")/bookstore/book • where$x/price>20 • orderby $x/title • return$x/title • forthe variable x in document "book.xml" return all the title elements where the price of the book is greater than 20and order the result with respect to the title. <title>Harry Potter</title> <title>Learning XML</title>

18. FLWOR IN HTML • We can showthequeryresult in Html format too. Forthiswewilluse <ul> and <li> tagsandwriteourcodebetweencurlybrackets {}. • <ul>{for $x in doc("books.xml")/bookstore/book/titleorder by $xreturn <li>{$x}</li>}</ul> • <ul><li><title>Harry Potter</title></li><li><title>Learning XML</title></li></ul>

19. FLWOR IN HTML • Ifwe do not wanttogettheresultbetween XML tagsweshouldusethedata. • <ul>{for $x in doc("books.xml")/bookstore/book/titleorder by $xreturn <li>{data($x)}</li>}</ul> • <ul><li>Harry Potter</li><li>Learning XML</li></ul>

20. Example • <ul>{for $x in doc("books.xml")/bookstore/book/titleorder by $xreturn <li>{data($x)}</li>}</ul> • <ul><li>Harry Potter</li><li>Learning XML</li></ul> Expression in Html TheResult

21. Example • Instead of <ul> we can use <ol> tag. (ul=UnorderedList, ol=OrderedList) • Inthatwaytheresultwill be numbered • <ol>{for $x in doc("books.xml")/bookstore/book/titleorder by $xreturn <li>{data($x)}</li>}</ol> • <ol><li>Harry Potter</li><li>Learning XML</li></ol> Expression in Html TheResult

22. Selecting And Filtering Elements The for Clause • The for clause binds a variable to each item returned by the in expression. The for clause results in iteration. There can be multiple for clauses in the same FLWOR expression. • To loop a specific number of times in a for clause, you may use the tokeyword

23. Selecting And Filtering Elements The for Clause • for $x in (1 to 5)return <test>{$x}</test> • Result: <test>1</test><test>2</test><test>3</test><test>4</test><test>5</test>

24. Selecting And Filtering Elements The for Clause • The at keyword can be used to count the iteration: for$x at $i in doc("books.xml")/bookstore/book/titlereturn<book>{$i}. {data($x)}</book> • Result: <book>1. Harry Potter</book> <book>2. XQuery Kick Start</book> <book>3. Learning XML</book> • It is also allowed with more than one in expression in the for clause. Use comma to separate each in expression: for$x in (10,20), $y in (100,200)return<test>x={$x} and y={$y}</test> • Result: <test>x=10 and y=100</test> <test>x=10 and y=200</test> <test>x=20 and y=100</test> <test>x=20 and y=200</test>

25. Selecting And Filtering Elements The let Clause • The let clause allows variable assignments and it avoids repeating the same expression many times. The let clause does not result in iteration. let$x := (1 to 5)return<test>{$x}</test> • Result: <test>1 2 3 4 5</test>

26. Selecting And Filtering Elements The where Clause • The where clause is used to specify one or more criteria for the result: for $x in doc("books.xml")/bookstore/book/titlewhere $x/price>30 and $x/price<40 return$x/title • Result: <title lang="en">Harry Potter</title> <title lang="en">Learning XML</title>

27. Selecting And Filtering Elements The order by Clause • The order by clause is used to specify the sort order of the result. Here we want to order the result by category and title: for$x in doc("books.xml")/bookstore/bookorder by $x/@category, $x/titlereturn $x/title • Result: <title lang="en">Harry Potter</title> <title lang="en">Learning XML</title> <title lang="en">XQuery Kick Start</title>

28. Selecting And Filtering Elements The return Clause • The return clause specifies what is to be returned. for$x in doc("books.xml")/bookstore/book return$x/title • Result: <title lang="en">Harry Potter</title><title lang="en">XQuery Kick Start</title><title lang="en">Learning XML</title>

29. Adding Html elements and text to result If we want to add some HTML elements to the result. We will put the result in an HTML list - together with some text: <html><body><h1>Bookstore</h1> <ul> { for $x in doc("books.xml")/bookstore/book order by $x/title return <li>{data($x/title)}. Category: {data($x/@category)}</li> } </ul></body></html>

30. Adding Html elements and text to result The XQuery expression above will generate the following result: <html><body><h1>Bookstore</h1><ul><li>Harry Potter. Category: CHILDREN</li><li>Learning XML. Category: WEB</li><li>XQuery Kick Start. Category: WEB</li></ul></body></html>

31. Adding Attributes to HTML Elements • If we want to use the category attribute as a class attribute in the HTML list: • <html><body><h1>Bookstore</h1> <ul> { for $x in doc("books.xml")/bookstore/book order by $x/title return <li class="{data($x/@category)}">{data($x/title)} </li> } </ul></body></html>

32. Adding Attributes to HTML Elements • The XQuery expression above will generate the following result: • <html><body><h1>Bookstore</h1><ul><li class="CHILDREN">Harry Potter</li><li class="WEB">Learning XML</li><li class="WEB">XQuery Kick Start</li></ul></body></html>

33. Xquery Syntax • XQuery is case-sensitive • XQuery elements, attributes, and variables must be valid XML names • An XQuery string value can be in single or double quotes • An XQuery variable is defined with a $ followed by a name, e.g. $bookstore • XQuery comments are delimited by (: and :) e.g. (: XQuery Comment :)

34. Xquery Conditional Expressions • XQuery supports the following conditional if-then-else statement if (<expression1>) then <expression2> else <expression3> • The test expression must be enclosed between parentheses. • The else expression is required. If you do not need it, you can return " ( )

35. Xquery Conditional Expressions • If expression1 results in an empty sequence, the result is False. • If expression1 results in a simple Boolean value, this value is the result of the expression. • Ifexpression1 results in a sequence of one or more nodes, the result of the expression is True. • Otherwise, a static error is raised.

36. Xquery Conditional Expressions • Example for $x in doc("books.xml")/bookstore/book return if ($x/@category="CHILDREN") then <child>{data($x/title)}</child> else <web>{data($x/title)}</web> • Result: <child>Harry Potter</child> <web>Learning XML</web>

37. Xquery Conditional Expressions • More Examples • Conditional expression returning multiple expressions for $prod in (doc("catalog.xml")/catalog/product) returnif ($prod/@dept = 'ACC') then( <accessoryNum>{data($prod/number)}</accessoryNum>, <accessoryName>{data($prod/name)}</accessoryName> ) else <otherNum>{data($prod/number)}</otherNum>

38. Xquery Conditional Expressions • More Examples • Nested conditional expressions for $prod in (doc("catalog.xml")/catalog/product) returnif ($prod/@dept = 'ACC') then<accessory>{data($prod/number)}</accessory> else if ($prod/@dept = 'WMN') then<womens>{data($prod/number)}</womens> else if ($prod/@dept = 'MEN') then<mens>{data($prod/number)}</mens> else<other>{data($prod/number)}</other>

39. Xquery Comparisons 1. General comparisons: =, !=, <, <=, >, >= • When you are comparing two sequences by using general comparison operators and a value exists in the second sequence that compares True to a value in the first sequence, the overall result is True. Otherwise, it is False. Example: (1, 2, 3) = (3, 4) is True, because the value 3 appears in both sequences. 2. Value comparisons:eq, ne, lt, le, gt, ge • If the two values compare the same according to the chosen operator, the expression will return True. Otherwise, it will return False. If either value is an empty sequence, the result of the expression is False. • The following expression returns true if any q attributes have a value greater than 10: $bookstore/book/@q > 10 • The following expression returns true if there is only one q attribute returned by the expression, and its value is greater than 10. If more than one q is returned, an error occurs: $bookstore//book/@q gt 10

40. XqueryFunctions The URL of the XQuery function namespace is: http://www.w3.org/2005/02/xpath-functions The default prefix for the function namespace is ‘fn:’. Functions are often called with the fn: prefix, such as fn:string(). However, since fn: is the default prefix of the namespace, the function names do not need to be prefixed when called.

41. XqueryFunctions Functioncalls can appear: • In an element: • <name>{uppercase($booktitle)}</name> • Inthepredicate of a pathexpression: • doc("books.xml")/bookstore/book[substring(title,1,5)='Harry'] • In a letclause: • let $name := (substring($booktitle,1,4))

42. XqueryUser-DefinedFunctions • Syntax: declare function prefix:function_name ($parameter ASdatatype)AS returnDatatype{ ...function codehere...}

43. Example of User-DefinedFunctions declare function local:minPrice ($p as xs:decimal?,$d as xs:decimal?)AS xs:decimal? { let $disc := ($p * $d) div 100 return ($p - $disc) } How to call the function above: <minPrice>{local:minPrice($book/price,$book /discount)}</minPrice>

44. Xquery&RelationalDatabase • RelationalDBsmanagestructured data andtheyarebeingusedby 80% of the market. • MostrelationalDBsdeveloppedcapabilitiesthat fit XML structured data concept • Recentlytherehavebeenattemptstodealwith data thatdoesn’t fit thatconcept

45. Xquery&RelationalDatabase • Databasessupport data to be stored in XML format. Therearethreebasictypes of storing data in XML format: • Nativestorage as xml data type • Mapping between XML and relational storage • Large object storage, [n]varchar(max) and varbinary(max)

46. Mapping of XML andRelationalStorage

47. Query CREATE VIEW dept_xmlview AS SELECT XMLElement("Department", XMLAttributes(deptno as "Deptno"), XMLElement("DeptInfo", XMLForest(dname as "DepartName", loc as "Location")), (SELECT XMLAgg(XMLElement("Employee", XMLAttributes(empno as "Empid"), XMLForest(ename as "EmpName", job as "Job, sal as "Salary"))) FROM emp e WHERE e.deptno = d.deptno)) AS department FROM dept d

48. Result <EmployeeEmpid="7934"> <EmpName>MILLER</EmpName> <Job>CLERK</Job> <Salary>1300</Salary> </Employee> </Department> • <DepartmentDeptno="40"> • <DeptInfo> • <DepartName>OPERATIONS</DepartName> • <Location>BOSTON</Location> • </DeptInfo> • <EmployeeEmpid=7954> • <EmpName>SMITH</EmpName> • <Job>VP</Job> • <Salary>4900</Salary> • </Employee> • </Department> <DepartmentDeptno="10"> <DeptInfo> <DepartName>ACCOUNTING</DepartName> <Location>NEW YORK</Location> </DeptInfo> <EmployeeEmpid="7782"> <EmpName>CLARK</EmpName> <Job>MANAGER</Job> <Salary>2450</Salary> </Employee> <EmployeeEmpid="7839"> <EmpName>KING</EmpName> <Job>PRESIDENT</Job> <Salary>5000</Salary> </Employee>

49. MoreExamplesEmp

50. MoreExamples SalGrade Dept