ASE114 - Querying and Transforming XML using XQuery and SQLX

1. ASE114 - Querying and Transforming XMLusing XQuery and SQLX Phil Shaw 925.236.5174 phil.shaw@sybase.com

2. XML and SQL This talk describes new SQL extensions for XML • Generating XML from SQL • A new for xml clause to generate standard SQLX-XML from SQL • ProcessingXML stored in SQL • A new xmltest predicate to query stored XML • The new xmlextract function to select elements from stored XML: • Storing XML in SQL • A new xmlparse function to store complete XML documents

3. XML Standards The SQL extensions for XML are based on XML standards • SQLX – See http://sqlx.org and http://sqlstandards.org • XPath/XQuery – See http://www.w3.org

4. Topics… Mapping SQL to XML • The for xml clause and SQLX mappings XML Query • XPath language • XQuery language • Using XML Queries in SQL • Other SQLX functions

5. SQLX The SQLX Standard is a new part of the ANSI SQL Standard • ISO/ANSI SQL/2003 It specifies two capabilities • Mappings of SQL tables to XML documents • Functions to build XML fragments from SQL

6. SQLX mappings XML markup for SQL objects • Catalogs • SQL schemas • Tables • Names, values, and datatypes Uses for SQLX documents • Standard XML interchange format • Standard XML view of SQL data • Map to other formats with normal tools

7. The for xml clause The “for xml” clause is a new clause for SQL select statements select * from emp, dept where emp.deptno = dept.deptno forxml A select that specifies “for xml” can have the normal clauses joins group by having order by

8. for xml: Simple Example The for xml clause maps an SQL result to an SQLX document • select 11 as a, 12 as b • union • select 21, 22 • forxml The resulting SQLX-XML document for the above is <resultset> <row> <a>11</a> <b>12</b> </row> <row> <a>21</a> <b>22</b> </row> </resultset>

9. for xml options The SQLX mappings have many options You specify for xml options with the option clause • select * from some_table • forxmloption “…options…” The next few slides describe the major for xml options

10. for xml options: columnstyle option “columnstyle=element” (default) select * from t forxmloption “columnstyle=element” option “columnstyle=attribute” select * from t forxmloption “columnstyle=attribute”

11. for xml options: columnstyle option “columnstyle=attribute” <resultset> <row a=“11” b=“12”/> <row a=“21” b=“22” /> </resultset> option “columnstyle=element” <resultset> <row> <a>11</a> <b>12</b> </row> <row> <a>21</a> <b>22</b> </row> </resultset>

12. for xml options: nullstyle option “nullstyle=omit” (default) • select 11 as a, null as b • unionselect null, 22 • forxmloption “nullstyle=omit” option “nullstyle=attribute” • select 11 as a, null as b • unionselect null, 22 • forxmloption “nullstyle=attribute”

13. for xml options: nullstyle option “nullstyle=omit” <resultset> <row> <a>11</a> </row> <row> <b>22</b> </row> </resultset> option “nullstyle=attribute” <resultset> <row> <a>11</a> <b nil=“true”/> </row> <row> <a nil=“true”/> <b>22</b> </row> </resultset>

14. for xml options: incremental option “incremental=no” - returns whole resultset in 1 row • select * from t • forxmloptions “incremental=no” option “incremental=yes” - returns resultset row-by-row • select * from t • forxmloptions “incremental=yes”

15. for xml: other options Other for xml options • binary= {hex | base64} • tablename=name • rowname=name • prefix=name • schemaloc=URL • statement={yes | no} • targetns=URL • root={yes | no} Other SQLX mapping functions forxmldtdj: To generate an XML DTD forxmlschemaj: To generate an XML schema

16. for xml: Specifying options in variables Put common options in a variable declare @opt varchar(200) select @opt = “columnstyle=attribute, • binary=base64, rowname=item” select * from emp,dept forxmloption @opt+”tablename=emp_dept”

17. Mapping SQLX back to SQL SQLX documents represent SQL tables You can map SQLX documents back to SQL tables • The forsqlscriptj function • Map an SQLX document to an SQL script • Create and populate a table with the data

18. forsqlscriptj: Input SQLX doc Input SQLX document <resultset> <row> <a>11</a> <b>12</b> </row> <row> <a>21</a> <b>22</b> </row> </resultset> Generated SQL script create table "resultset"( "a" integer not null, "b" integer not null ) insert into "resultset“ ("a", "b") values ( 11, 12) insert into "resultset“ ("a", "b") values ( 21, 22)

19. …Topics… Mapping SQL to XML • The for xml clause and SQLX mappings XML Query • XPath language • XQuery language • Using XML Queries in SQL • Other SQLX functions

20. XML Query Language XPath • Used to reference document elements • Provides a common base: • XSLT • XQuery XQuery • Computational extension of XPath.

21. XPath elements XPath has the following elements • Simple paths • Wild cards • Descendant paths • Attributes • Subscripts • Predicates

22. XPath elements Here are skeletal forms of those elements • Simple paths /A/B/C • Wild cards /A/*/C • Descendant paths /A//F/G • Attributes /A/B/@T • Subscripts /A/B[1]/C • Predicates /A[B=3]/C /A[//F/@T=4]/C

23. Example Data For XML examples, we’ll use a “bookstore.xml” document • This is a common sample document for XPath & XQuery,

24. Portion of a “bookstore.xml” document <bookstore specialty='novel'> <book style='textbook'> • <title>History of Trenton</title> • <author> <name>Mary Bob</name> <publication>Selected Short Stories of <name>Mary Bob</name> </publication> • </author> • <author><name>James Bob</name> </author> • <price discount=“0.05”>55</price> </book> <book> ETC </book> <magazine><title> ETC </title> </magazine> </bookstore>

25. Simple paths: /A/B/C “/” means next containing level /bookstore/book/price /bookstore/book/author/name

26. Wild cards: /A/*/C “*” means any element names • /bookstore/book/title • /bookstore/magazine/title • /bookstore/*/title

27. Descendant paths: /A//F/G “//” means any contained level • /bookstore/book/author/name • /bookstore/book/author/publication/name • /bookstore/book/author//name • /bookstore/book//name • /bookstore//name • //name

28. Attributes: /A/B/@T Prefix attribute names with “@” /bookstore/@specialty /bookstore/book/@style /bookstore/book/price/@discount Also reference attributes (“@”) with descendant (“//”) //@specialty //@style //@discount

29. Subscripts: /A[0]/B[2]/C[1] Reference a particular element in a list with a subscript /bookstore/book/author/name /bookstore/book[4]/author/name /bookstore/book[4]/author[1]/name Subscripts begin with zero /bookstore/book[0]/author[1]/name There’s always an element zero /bookstore[0]/book[0]/author[1]/name[0]

30. Subscripts: negative & range: /A/B/C[-1] Reference elements from the end as –1, -2, etc /bookstore/book[0]/author[-1]/name /bookstore/book[-1]/author[-2]/name Reference ranges of elements with “to” /bookstore/book[2 to 4]/author[-1]/name /bookstore/book[2 to 4]/author[-2 to -1]/name

31. Predicates: /A/B[Cx=“3” or Cy=“5”] Filter elements with predicates in “[…]” • /bookstore/book[title="History of Trenton"]/@style • /bookstore/book[@style="textbook"]/title • /bookstore/book[@style=“textbook" and title=“History of Trenton"] /author[name=“Mary Bob"]/publication • /bookstore/book[//name=“Mary Bob”]/price/@discount • /bookstore/book[price=“12” and price/@discount=“0.05”]//publication

32. …Topics… Mapping SQL to XML • The for xml clause and SQLX mappings XML Query • XPath language • XQuery language • Using XML Queries in SQL • Other SQLX functions

33. XQuery language XPath expressions are the basic elements of XQuery XQuery adds “FLWOR” expressions for… let… where… order by… return…

34. XQuery & SQL

35. XQuery example A simple XQuery <result> • let $bks := document("bookstore.xml") • <specialty>{$bks/@specialty}</specialty> • {for $bk in $bks/book • where $bk/price < 100" • return <book> <title>{$bk/title}</title> <price tax="{$bk/price * 0.08 }">{$bk/price}</price> </book>} </result>

36. Format of the example result The example XQuery would results of the form <result> • <specialty>novel</specialty> • <book> <title>History of Trenton</title> <price tax=“4.40”>55</price> • </book> • <book> ETC </book> • ETC </result>

37. …Topics… XML Query • XPath language • XQuery language • Using XML Queries in SQL SQLX • Mapping functions • Other functions

38. Using XML queries in SQL XML query as a boolean predicate query xmltest document Retrieve XML query results into SQL xmlextract(query, document)

39. The xmltest predicate Similar to the like predicate select * from bookstore_table where ‘//price/@discount ‘ xmltest bookstore_doc Empty results are treated as false

40. The xmlextract function Similar to the substring function select xmlextract(‘//book[title=“History of Trenton”]/author’, bookstore_doc) from bookstore_table

41. xmlextract & xmltest Use variables for longer XPath expressions declare @xq varchar(1000) select @xq = ‘/bookstore/book • [@style=“textbook" and title=“History of Trenton"] • /author[name=“Mary Bob"]/publication’ declare @tq varchar(1000) select @tq = ‘/bookstore[@specialty=“novel”] ‘ select xmlextract(@xq, bookstore_doc) from bookstore_table where @tq xmltest bookstore_doc

42. The xmlparse function XML documents can be stored either • As character text • As parsed XML xmltest and xmlextract can process either form • Parsed XML is more efficient for repeated access Use the xmlparse built-in function to parse XML documents • xmlparse(doc)

43. …Topics… Mapping SQL to XML • The for xml clause and SQLX mappings XML Query • XPath language • XQuery language • Using XML Queries in SQL • Other SQLX functions

44. Other SQLX functions The SQLX standard also specifies new built-in functions • xmlconcat • xmlelement • xmlforest • xmlgen

45. xmlconcat xmlconcat is a specialized concatenation for XML • Ignores null operands • Ensures only one XML header E.g. xmlconcat(name, address)

46. xmlelement xmlelement assembles an XML element from SQL values select xmlelement(name “salary” xmlattributes(period_col as period, currency_col as currency), salary_col) from … Equivalent SQL select ‘<salary period=“’ + period_col + ‘” currency=“’ + currency_col + ‘”>’ • + salary_col + ‘</salary>’ from … Result Assume period_col is ‘week’, currency_col is :euro’, and salary_col is 123.45 <salary period=“week” currency=“euro”>123.45 </salary>

47. xmlforest xmlforest is a shorthand for xmlelement & xmlconcat xmlforest(“John Doe” as name, 37 as age, 123.45 as salary) Result <name>John Doe</name> <age>37</age> <salary>123.45</salary>

48. xmlgen xmlgen is like xmlextract, for executing XQuery expressions Here is a variable @q with the XQuery expression we saw earlier declare @q varchar(10000) select @q = ‘<result> • <specialty>{$bks/@specialty}</specialty> • {for $bk in $bks/book • where $bk/price < $price_limit • return <book> <title>{$bk/title}</title> <price tax="{$bk/price * 0.08 }">{$bk/price}</price> </book>} </result>’

49. xmlgen The following executes the @q query selectxmlgen(@q, bookstore_doc as bks, 100 as price_limit) from bookstore_table The xmlgen call supplies variables $bks and $price_limit • booksgtore_doc is referenced as $bks • 100 is referenced as $price_limit

50. …Topics Mapping SQL to XML • The for xml clause and SQLX mappings XML Query • XPath language • XQuery language • Using XML Queries in SQL • Other SQLX functions