1 / 50

ASE114 - Querying and Transforming XML using XQuery and SQLX

ASE114 - Querying and Transforming XML using XQuery and SQLX. Phil Shaw 925.236.5174 phil.shaw@sybase.com. 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 Processing XML stored in SQL

tilden
Download Presentation

ASE114 - Querying and Transforming XML using XQuery and SQLX

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related