1 / 44

XML Publishing

XML Publishing. Introduction General approach XPERRANTO SilkRoute Microsoft SQL 2000 Summary. Introduction. What is XML Publishing? XML Publishing is the task of transforming the relational data into XML, for the purpose of exchange over the Internet.

whitley
Download Presentation

XML Publishing

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. XML Publishing Introduction General approach XPERRANTO SilkRoute Microsoft SQL 2000 Summary

  2. Introduction What is XML Publishing? XML Publishing is the task of transforming the relational data into XML, for the purpose of exchange over the Internet. More specifically, publishing XML data involves joining tables, selecting and projecting the data that needs to be exported, creating XML hierarchies; and processing values in an application specific manner.

  3. Introduction • Why need XML Publishing? - most business data are stored in relational database systems. - XML is a standard for exchanging business data on the web. - it’s a simply, platform independent, unicode based syntax for which simple and efficient parsers are widely available. - it can not only represent structured data, but also provide an uniform syntax for semi-structured data and marked-up content.

  4. Introduction Two data model: • Relational data - fragmented into many flat relations - normalized - proprietary • XML data - nested - un-normalized - public (450 schemas at www.biztalk.org)

  5. General Approach • Create XML views over Relational Data, each of these XML views can provide an alternative, application-specific view of the underlying relational data. Through these XML views, business partners can access existing relational data as though it was in some industry-standard XML format.

  6. Virtual vs. Materialize • Materialized XML Publishing Materialize the entire XML view on request and return the resulting XML document. • Virtual XML Publishing Support queries over XML views, return what user applications actually want.

  7. Virtual vs. Materialize • Materialized XML Publishing - applications can access all the data without interfering with the relational engine - XML view need to be refreshed periodically - inefficient in some cases • Virtual XML Publishing - guarantee data freshness - leverage the processing power of relational engines - translation of an XML query of an XML view into SQL may be complex

  8. Middleware System • Interface between Relational Database and User Application - defines and manages XML views - translates incoming XML queries into SQL and submits them to the database system - receives the queries’ results, then translates them back into XML terms.

  9. Applications Middleware System Web/Intranet User XML Queries Result XML Documents XML Query Processor View Definition XML Views Manager XML Tagger View Description SQL Queries Tuples Streams Figure 1 A high-level architecture of middleware system RDBMS

  10. XPERRANTO vs. SilkRoute • IBM XPERRANTO - pure XML, single query language approach. XML views are defined by XML query language which is using the type system of XML schema. • SilkRoute -XML views are defined using a declarative query language called RXL (Relational to XML Transformation Language).

  11. XPERRANTO vs. SilkRoute • XPERRANTO - user only need be familiar with XML -both relation data and meta-data can be represented and queried in the same framework -can publish object-relational structures -pushes all relational logic down to database engine

  12. Query Translation View Definition XML View Services XML-QL Parser View Description XQGM XML Schema Query Rewrite XML Schema Generator Catalog Info. XQGM XML Result SQL Translation XML Tagger Data Tuples O-R Database SQL Query Processor SQL Queries StoredTables System Catalog Figure 2 XPERRANTO Architecture

  13. Example 1: Relational Schema vs. XML View Schema • DDL (Data Definition Language) for O-R Schema in SQL99 Terms 1.Create TableBookAS (bookID CHAR(30), name VARCHAR(225), publisher VARCHAR(30)) 2.Create TablepublisherAS (name VARCHAR(30), address VARCHAR(255)) 3.Create Typeauthor_typeAS (bookID CHAR(30), first VARCHAR(30), last VARCHAR(30)) 4.Create TableauthorOFauthor_type (REF IS ssn USER GENERATED)

  14. XML View Schema over Example O-R database Create Table bookAS... <simpleType name=“string255” source=“string”> <maxlength value=“255”/> </simpleType> <simpleType name=“string30” source=“string”> <maxlength value=“30”/> </simpleType> <complexType name=“bookTupleType”> <element name=“bookID” type=“string30”/> <element name=“name” type=“string225”/> <element name=“publisher” type=“string30”/> </complexType> <complexType name=“bookSetType”> <element name=“bookTuple” type=“bookTupleType” maxOccurs=“*”/> </complexType> <element name=“book” type=“bookSetType”/> <complexType name=“author_type”> <element name=“bookID” type=“string30”/> <element name=“first” type=“string30”/> <element name=“last” type=“string30”/> </complexType> <complexType name=“authorTupleType” source=“author_type” derivedBy=“extension”> <attribute name=“ssn” type=“ID”/> </complexType> <complexType name=“authorSetType”> <element name=“authTuple” type=“authTupleType” maxOccurs=“*”/> </complexType> <element name=“author” type=“authSetType”/> Create Typeauthor_typeAS... Create Table authorOF ...

  15. Default XML View over Example O-R database <db> <book> <row><bookID>…</bookID><name>…</name><publisher>…</publisher></row> <row><bookID>…</bookID><name>…</name><publisher>…</publisher></row> … </book> <author> <row><ssn>…</ssn><bookID>…</bookID><first>…</first><last>…</last></row> <row><ssn>…</ssn><bookID>…</bookID><first>…</first><last>…</last></row> … </author> <publisher> …similar to <book> and <item> </publisher> </db>

  16. Example 2: From XQuery to SQL Query Result XQuery XPERRANTO Query Engine XQuery Parser XQGM Query Rewrite & View Composition XQGM Computational Pushdown Tagger Graph Tagger Runtime SQL Query Tuples RDBMS

  17. order item payment custname id custnum oid desc cost oid due amt A Purchase Order Database and its Default View 10 Smith Construction 7734 10 generator 10 1/10/01 8000 20000 backhoe 12000 6/10/01 12000 9 Western Builders 7725 10 10 <db> <order> <row><id>10</id><custname>Smith Construction</custname><custnum>7734</custnum></row> <row><id>9</id><custname>Western Builders</custname><custnum>7725</custnum></row> </order> <item> <row><oid>10</id><desc>generator</desc><cost>8000</cost></row> <row><oid>10</id><desc>backhoe</desc><cost>24000</cost></row> </item> <payment> …similar to <order> and <item> </payment> </db>

  18. 01. create view orders as ( 02. for $order in view (“default”)/order/row 03. return 04. <order id=$order/id> 05. <customer>$order/custname</customer> 06. <items> 07. for $item in view(“default”)/item/row 08. where $order/id=$item/oid 09. return 10. <item description=$item/desc> 11. <cost>$item/cost</cost> 12. </item> 13. </items> 14. <payments> 15. for $payment in view(“default”)/payment/row 16. where $order/id=$payment/oid 17. return 18. <payment due=$payment/data> 19. <amount>$payment/amount</amount> 20. </payment> 21. sortby(@due) 22. </payments> 23. </order>) XML Purchase Order <order id=“10”> <customer>Smith Construction</customer> <items> <item description=“generator”> <cost>8000</cost> </item> <item description=“backhoe”> <cost>24000</cost> </item> </items> <payments> <payment due=“1/10/01”> <amount>20000</amount> </payment> <payment due=“6/10/01”> <amount>12000</amount> </payment> </payments> </order> <order id=“9”> … </order> User-defined XML “orders” view

  19. XQuery over “orders” view 1. for $order in view(“orders”) 2. where $order/customer/text() like “Smith%” 3. return $order XQuery Parser

  20. Query Parsing • XQGM (XML Query Graph Model) - extension of a SQL internal query representation called Query Graph Model (QGM). - consists of a set of operators and functions that are designed to capture the semantics of an XML query.

  21. Part of the XML Functions and Operators in XQGM

  22. view result XQGM for the XML Orders View $order project: $order= <order id=$id> <custname>$custname</custname> <items>$items</items> <payments>$pmts</payments> </order> 11 $custname $id $items $pmts correlation on order.id 10 join (correlated): $pmts $items groupby: orderby (on $due): $pmts = aggXMLFrags($pmt) groupby: $items = aggXMLFrags($item) 9 5 $due $item $pmt 8 project: $pmt = <payment> … project: $item = <item> … 4 $desc $cost $due $amt 7 select: $oid = $id select: $oid = $id 3 $desc $cost $custname $oid $id $oid $due $amt table: item table: order table: payment 2 1 6

  23. for $order in view(“order”) where $order/customer/text() like “Smith%” return $order $order 8 join (correlated): $val 7 select: isText($val) and $val like “Smith%” $val Unnest: $val = unnest($vals) 6 $vals project: $vals = getContents($elem) correlation on $order 5 $elem select: isElement($elem) and getTagName($elem) = “customer” 4 $elem 3 Unnest: $elem = unnest($elems) $elems 2 project: $elems = getContents($order) XQGM for the Query over Orders View $order View: orders 1

  24. View Composition • XQGM after the Query Parsing Stage is composed with the views it references (orders view here) and rewrite optimizations are performed to eliminate the construction of intermediate XML fragments and push down predicates.

  25. View Composition Composition Rules

  26. View $order $custname $order Query join (correlated): 13 project: $order= <order id=$id>… 11 $custname $custname $id $items $pmts 12 Select: $custname like “Smith%” correlation on order.id 10 join (correlated): $pmts $items groupby: orderby (on $due): $pmts = aggXMLFrags($pmt) groupby: $items = aggXMLFrags($item) 5 9 $due $item $pmt 4 8 project: $pmt = <payment> … project: $item = <item> … $desc $cost $due $amt 7 3 select: $oid = $id select: $oid = $id $custname $id Select: $custname like “Smith%” Predicate pushdown $desc $cost $custname $oid $id $oid $due $amt table: item table: order table: payment 2 1 6

  27. Computation Pushdown • The goal in this phase of query processing is to push all data and memory intensive operations down to the relational engine as an efficient SQL query. Two techniques are available: • Query Decorrelation • Tagger Pull-up

  28. Query Decorrelation • Complex expressions in Xquery can be represented using correlations. However, it has been shown in earlier work that executing correlated XML queries over a relational database leads to poor performance, so query de-correlation is a necessary step for efficient XML query execution.

  29. $order XQGM after Decorrelation project: $order= <order>… 13 $custname $order $items $pmts left outer join: $id = $id 12 $items $custname $id right outer join: $id = $id 11 $pmts $id $id $items groupby: orderby (on $due): $pmts = aggXMLFrags($pmt) Groupby (on $id) : $items = aggXMLFrags($item) 5 9 $due $id $item $id $pmt 4 8 project: $pmt = <payment> … project: $item = <item> … $desc $id $cost $id $due $amt 7 3 join: $oid = $id join: $oid=$id $custname $id Select: $custname like “Smith%” 10 $desc $cost $custname $oid $id $oid $due $amt table: item table: order table: payment 2 1 6

  30. Tagger Pull-up • This step comes right after the query decorrelation. It separates the tagger and SQL operations before SQL query are generated • Relational operations are pushed to the bottom of the graph. SQL statements are generated and sent to the relational engine for execution. • XML construction functions are pulled up to the top of the query graph and transformed into a “tagger run-time” graph, which produces the result XML documents.

  31. $order XQGM after Tagger Pull-up correlation on id 8 Merge: $order=<order>… $items $pmts aggregate:: $pmts = aggXMLFrags($pmt) aggregate: $items = aggXMLFrags($item) 7 4 $item $pmt 6 merge: $pmt = <payment> … merge: $item = <item> … 3 $desc $cost $due $amt 5 $custname $id input: $oid = $id input: $oid = $id 2 input: 1 select p.oid, p.due, p.amt from payment p, order o where o.custname like ‘Smith%’ and p.oid = o.id order by o.id, p.due select p.oid, i.desc, i.cost from item i, order o where o.custname like ‘Smith%’ and i.oid = o.id order by o.id select o.id, o.custname from order o where o.custname like ‘Smith%’ order by o.id

  32. SilkRoute Approach

  33. Applications Web/Intranet User XML Queries Result XML Documents Virtual View Or Materialized View RXL SilkRoute Query Composer XML Tagger Query RXL Plan Generator XML Template SQL Queries Tuples Streams Source Description XML RDBMS SilkRoute’s Architecture

  34. SilkRoute Approach • Database administrator starts by writing an RXL query that defines the XML view of the database. It is called the view query. • A materialized view is fed directly into the Plan Generator, which generates a set of SQL queries and one XML template. • A virtual view is first composed by the Query Composer with a user query resulting another RXL query which then is fed into Plan Generator. • SQL queries are sent to the RDMS server, which returns one sorted tuple stream per SQL query • XML Tagger merges the tuple streams and produces the XML document, which is returned to the application.

  35. Query Composer • This component takes a user XML-QL query and composes it with the RXL view query resulting a new RXL query. It combines fragments of the view query and user query. Works the similar way that the Query Parser and Query Rewrite components in XPERRANTO do.

  36. Plan Generator • This component in SilkRoute uses a greedy optimization algorithm to choose an optimal set of SQL queries for a given RXL view definition. The algorithm bases its decisions on query cost estimations provided by the relational engine and can return more than one plan, which will be integrated with additional optimization algorithms that optimize specific parameters, such as network traffic or server load. Details of the greedy algorithm can be found in: Efficient evaluation of XML middle-ware queries. M. Fernandez etc.

  37. XML Publishing : SQL Server Two approaches • SQL-centric approach extend the function of SQL queries to realize the transformation. The extended version of SQL query is called “FOR XML”. • Virtual XML views approach use XDR (XML-based XML-Data Reduced) schema language to define virtual XML views over relation database, then do querying with XPath.

  38. XML Publishing : SQL ServerSQL-centric approach Three modes • RAW mode • Auto Mode • Explicit Mode

  39. XML Publishing : SQL Server, RAW Mode SELECT CustomerID, OrderID FROM Customer LEFT OUTER JOIN ORDERS ON Customers.CustomerID = Orders.CustomerID For XML Raw <row CustomerID = “ALFKI”, OrderID = “10643”/> <row CustomerID = “ALFKI”, OrderID = “10692”/> <row CustomerID = “ANATR”, OrderID = “10308”/> . . . . • flat XML • default tag and attribute names

  40. XML Publishing : SQL ServerAuto Mode SELECT Customers.CustomerID, OrderID FROM Customer LEFT OUTER JOIN ORDERS ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.OrderID For XML Auto <Customers CustomerID = “ALFKI”> <Orders OrderID = “10643”/> <Orders OrderID = “10692”/> </Customers> <Customers CustomerID = “ANATR”> <Orders OrderID = “10308”/> </Customers> . . . . • default tag and attribute names • no differently typed sibling elements

  41. XML Publishing : SQL ServerExplicit Mode • Nested XML • User defined tags and attributes • Idea: write SQL queries with complex column names • Ad-hoc, order dependent semantics

  42. XML Publishing : SQL ServerVirtual XML Views • The core mechanism of providing XML views over relation data is the concept of an annotated schema, which consist of a schema description of the XML view and annotations that describe the mapping of the XML schema constructs to the relational schema constructs. Then the XPath query together with the annotated schema is translated into a FOR XML query that only returns the data that is required by the query.

  43. Summary • IBM XPERRANTO pure XML, single query language approach. XML views are defined by XML query language which is using the type system of XML schema. • SilkRoute XML views are defined using a declarative query language called RXL (Relational to XML Transformation Language). • Microsoft SQL 2000 Supports queries over XML views, but the support is very limited, because queries are specified using XPath, which is a subset of XQuery.

  44. Future Work • IBM XPERRANTO - provides support for insertable and updateable XML views - pushes tagging inside the database system • SilkRoute - looks for better algorithms for translating of RXL into efficient SQL and minimization of composed RXL views • Microsoft SQL 2000 - finds out whether query composition and decomposition is possible for the complete XQuery language or for only a subset of the language

More Related