1 / 29

SQL/XML, XQuery , and Native XML Programming Languages

<root> <presentation> <author> William Beaumont </author> <presenter> William Beaumont </presenter> <based-on idref = " thePaper " /> </presentation> <paper id = " thePaper " > <title> SQL/XML, Xquery , and Native XML Programming Languages </title> <author> <name> Jonathan Robie </name>

minya
Download Presentation

SQL/XML, XQuery , and Native XML Programming Languages

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. <root> <presentation> <author>William Beaumont</author> <presenter>William Beaumont</presenter> <based-onidref="thePaper"/> </presentation> <paperid="thePaper"> <title>SQL/XML, Xquery, and Native XML Programming Languages</title> <author> <name>Jonathan Robie</name> <company>DataDirect Technologies</company> </author> <yearPublished>2003</yearPublished> <publisher>IDEAlliance</publisher> <publishingVenue>XML Conference &#38; Exposition 2003</publishingVenue> <link> http://www.idealliance.org/papers/dx_xml03/papers/05-02-01/05-02-01.pdf </link> </paper> </root> SQL/XML, XQuery, and Native XML Programming Languages

  2. Written and presented by William Beaumont Based on the article byJonathan RobieofDataDirect Technologies, published 2003 by IDEAlliance at XML Conference & Exposition 2003 (http://www.idealliance.org/papers/dx_xml03/papers/05-02-01/05-02-01.pdf) SQL/XML, XQuery, and Native XML Programming Languages

  3. 1. Introduction • A web application usually uses XML to transfer data from itself to a relational database and vice versa • Every major database vendor uses its own proprietary approach to implementing this transference • There is no interoperability between the vendors’ approaches • Many developers need to make applications that work for databases from different vendors

  4. 1. Introduction (cont’d): XQuery and SQL/XML • XQuery and SQL/XML are separate standards for query languages that return queried data as XML • What both standards have in common: • The returned XML can have any desired structure • The queries can be arbitrarily complex • What differs between the standards: • XQuery is XML-centric • SQL/XML is SQL-centric

  5. 1. Introduction (cont’d): SQL/XML • SQL/XML is an SQL extension (specifically, it is part of ANSI/ISO SQL 2003) • It lets SQL queries create XML structures using several XML publishing functions • It’s easy to learn for an SQL programmer because it encompasses a small number of additions to the SQL language • Naturally, as an extension to SQL, it can be used in conjunction with any or all of SQL’s tools/infrastructure • For example, SQL/XML can be used with JDBC, SQL’s interface to Java • There is no equivalent standard API for XQuery (or at least there was none at the time of this article’s writing in 2003) • Also, SQL, being a mature language, has functionality not yet available to XQuery, such as updates or stored procedures

  6. 1. Introduction (cont’d): XQuery • XQuery uses XML as the basis for its data model and type system • It is currently under development by the World Wide Web Consortium (W3C) • It can be described as a Native XML Programming Language • XML:XQuery :: relational model:SQL • Many products and projects offer ways to query relational data using an XML view of the database • XQuery’s design was influenced by this relational-data-as-XML paradigm • Ultimately, XQuery lets you work in the XML world no matter what type of data you’re working with: XML, relational, object, and so on • XQuery and SQL/XML are more or less equivalent when it comes to queries based purely on relational data • XQuery has the advantage with queries that span relational and XML sources

  7. 2. XML and Relational • XML and relational databases are based on two very different data models • Relational model: • 2-dimensional tables • No hierarchy or significant order within tables • XML: • Trees • Order is significant • Hierarchy and sequence are the main ways to represent information

  8. 2. XML and Relational: Representation • XML’s hierarchical, ordered data model is more naturally suited to presentation than the relational model • Suppose we had the following tables in a relational database: Projects Customers If we wished to query all the information about each customer, including their associated projects, we would use the following query: • SELECT * FROM Customers c, Projects p • WHERE c.CustId=p.CustId • ORDER BY c.CustId, p.ProjId

  9. 2. XML and Relational: Representation (cont’d) • The result of this query would be: • Note the rows with duplicate information, which have been highlighted

  10. 2. XML and Relational: Representation (cont’d) • By contrast, the same information can be modeled in XML as follows: <customers> <customer id="1"> <name>Woodworks</name> <city>Baltimore</city> <projects> <project><name>Medusa</name></project> </projects> </customer> <customer id="4"> <name>Hardware Shop</name> <city>Washington</city> <projects> <project><name>Pegasus</name></project> <project><name>Typhon</name></project> </projects> </customer> <!--...--> </customers>

  11. 3. XML and Relational: Four Approaches • XML applications that use relational data can choose from four approaches: • Use an interface like JDBC together with a parser like SAX or DOM, and maybe even XSLT, to transform SQL results into XML • What if the desired information is spread across multiple tables? • Requires lots of tedious code • Use the XML extensions provided by database vendors • These extensions are proprietary • What if we want a database-independent solution? • Use SQL/XML • Requires little new learning for an experienced SQL programmer • Is supported by Oracle and IBM • Is not supported by Microsoft • Database-independent implementations are available for any major relational DB

  12. 3. XML and Relational: Four Approaches (cont’d) • Use XQuery • Native XML query language • Natural to learn for XML programmers • Very good for applications that process XML together with relational data • Support for XQuery among major vendors is limited • There is not yet a complete standardized API

  13. 4.1 SQL/XML: XML Publishing Functions

  14. 4.1 SQL/XML: XML Publishing Functions (cont’d) • Example: select xmlelement(name "CustomerProj", xmlforest(c.CustId, c.Name as CustName, p.ProjId, p.Name as ProjName)) from Customers c, Projects P where p.CustId=c.CustId order by c.CustId select * from Customers c, Projects p where c.CustId=p.CustId order by CustId, p.projId <CustomerProj> <CustId>1</CustId> <CustName>Woodworks</CustName> <ProjId>1</ProjId> <ProjName>Medusa</ProjName> </CustomerProj> <CustomerProj> <CustId>4</CustId> <CustName>Hardware Shop</CustName> <ProjId>2</ProjId> <ProjName>Pegasus</ProjName> </CustomerProj> ...

  15. 4.2 SQL/XML: The XML Datatype • The XML datatype is a datatype used in SQL in the same way we use integer or date • The output of XML publishing functions goes in one or more columns of the result set just like any other SQL function • These columns are tagged as having the XML datatype for the benefit of any applications that use the result set • For example, the query "select Name as CustomerName, xmlelement(name CustomerCity, City) as CustomerCity" would return this result set:

  16. 4.2 SQL/XML: The XML Datatype (cont'd) • This is the Java code that would use the result set and expect the XML datatype: import com.ddtek.jdbc.jxtr.XMLType; import org.w3c.dom.*; public class MyClass { // ... public void myMethod() { // ... Statement statement = connection.createStatement(); ResultSetresultSet = statement.executeQuery(query); while(resultSet.next()) { String customerName = resultSet.getString(1); XMLTypecustomerCityXML = (XMLType)resultSet.getObject(2); Document doc = customerCityXML.getDOM(); doSomethingUseful(customerName, doc); } // ... } // ... }

  17. 4.3 SQL/XML: Mapping Rules • XML publishing functions use SQL values to create XML values whose types come from W3C XML Schema • SQL/XML mapping rules describe how SQL values are mapped to and from XML values • They also describe how SQL metadata is mapped to and from W3C XML Schemas

  18. 4.3 SQL/XML: Mapping Rules (cont'd) • An example of a decision in SQL/XML mapping is whether to map a table to one element or to a forest of elements, with one tree for each row <Table> <row> <A>Value 1A</A> <B>Value 1B</B> <C>Value 1C</C> </row> <row> <A>Value 2A</A> <B>Value 2B</B> <C>Value 2C</C> </row> </Table> <Table> <A>Value 1A</A> <B>Value 1B</B> <C>Value 1C</C> </Table> <Table> <A>Value 2A</A> <B>Value 2B</B> <C>Value 2C</C> </Table>

  19. 4.3 SQL/XML: Mapping Rules (cont'd) • Mappings are also defined on the metadata level • SQL/XML defines how SQL datatypes are represented in XML Schema • Each SQL type is derived from an equivalent built-in W3C XML Schema type

  20. 4.3 SQL/XML: Mapping Rules (cont'd) Customer(id INTEGER, name CHAR(50)) <xsd:schemaxmlns:xsd="http://www.w3.org/2001/XMLSchema"> ... <xsd:simpleTypename="INTEGER"> <xsd:restrictionbase="xsd:int" /> </xsd:simpleType> <xsd:simpleTypename="CHAR_50"> <xsd:restrictionbase="xsd:string"> <xsd:lengthvalue="50" /> </xsd:restriction> </xsd:simpleType> ... <xsd:elementname="Customer"> <xsd:complexType> <xsd:sequence> <xsd:elementname="id" type="INTEGER" /> <xsd:elementname="name" type="CHAR_50" /> </xsd:sequence> </xsd:complexType> </xsd:element> ... </xsd:schema>

  21. 5. XQuery and Native XML Programming • XQuery takes sequences of XML nodes as input and returns a sequence of XML nodes • It also allows XML views of non-XML data and serialized forms of non-XML data • We can store, query, process, and exchange data as XML using XQuery • Conventional Web applications only exchange data as XML and use SQL for storing and querying and Java or C# for processing • This can result in mismatches

  22. 5.1 XQuery: Native XML Programming • Everything that is queried using XQuery must be processed as though it were XML • We can either serialize the data as XML or create an XML view of it • Most systems that use relational data use SQL/XML mappings to create XML views • XQuery's fundamental types: • Document nodes • Elements • Attributes • Processing instructions • Comments • Text nodes • W3C XML Schema types (integers, strings, dates, etc.) • XQuery doesn't require schemas, but a schema can be specified if a query must ensure consistent use of types • XML is the basis of XQuery's type system and data model, and so, like XSLT and XPath, it is considered a Native XML Programming Language • SQL is the basis of SQL/XML, and so it is not considered a Native XML Programming Language • SQL/XML is merely a bridge to XML

  23. 5.1 XQuery: Native XML Programming (cont'd) • XML is not Objects! • An XML parser like DOM treats XML as though objects were its fundamental type • This leads to messy code like this: Tree t = ParseXML("stock.xml"); PERatio = number(t.getmember("/stock/price")) / ((number(t.getmember("/stock/revenues") - number(t.getmember("/stock/expenses")) • The same code can be done much more simply in XQuery like this: let $stock := document('stock.xml')/stock return $stock/price div ($stock/revenue - $stock/expenses)

  24. 5.1 XQuery: Native XML Programming (cont'd) • XML is not just text! • The XML documents below are not the same textually, but logically they are identical: <item xmlns:dc="http://purl.org/dc/elements/1.1/"> <title>MetaData</title> <dc:date>2003-01-12T00:18:05-05:00</dc:date> <link>http://bitworking.org/news/8</link> <description>Upon waking, the dinosaur...</description> </item> <root:itemxmlns:bc="http://purl.org/dc/elements/1.1/" xmlns:root=""> <root:title>MetaData</root:title> <bc:date>2003-01-12T00:18:05-05:00</bc:date> <root:link>http://bitworking.org/news/8</root:link> <root:description>Upon waking, the dinosaur...</root:description> </root:item> • Using regular expressions to process the text of XML documents can thus be a programmer's nightmare

  25. 5.1 XQuery: What should a Native XML Programming Language do? • Easily find anything in an XML structure (i.e., XPath) • Easily create any XML structure • Easily combine and restructure information from XML sources • Example: for $c in $cut/row let $p := $proj/row[CustId = $c/CustId] return <customer> <custName>{ string($c/name) }</custName> <projName>{ string($p/name) }</projName> </customer> • Easily use XML data in expressions • E.g., we should be able to apply arithmetic operations directly to XML content, observing the data types of typed data • Schemas that have been applied to a query should have their type constraints enforced

  26. 5.2 XQuery and SQL/XML Views • XQuery has few advantages over SQL/XML in terms of creating XML structures from relational data • Below are the SQL/XML and XQuery implementations of a relational data query whose output is XML: select xmlelement(namecustomer, xmlattributes(c.CustIdasid), xmlforest(c.Nameasname, c.Cityascity), xmlelement(nameprojects, (selectxmlagg(xmlelement(nameproject, xmlattributes(p.ProjIdasid), xmlforest(p.Nameasname))) fromProjectsp wherep.CustId=c.CustId))) as "customer-projects" fromCustomersc for $c in $cust/row return <customer id="{$c/CustId}"> <name>{string($c/Name)}</name> <projects> { for $p in $proj/row where $p/CustId=$c/CustId return <project id="{$p/ProjId}" name="{$p/Name}" /> } </projects> </customer>

  27. 5.3 Spanning Sources: XQuery, Web Messages, and Databases • Since XQuery works well on SQL/XML views of relational data, its strength is in processing both XML and relational data • Suppose we need to process a request in the form of an XML file by querying a relational database and returning an XML response • Below is the XML request: <p:itinerary xmlns:p="http://travel.org/reservation/travel"> <p:departure> <p:departing>New York</p:departing> <p:arriving>Los Angeles</p:arriving> <p:departureDate>2001-12-14</p:departureDate> <p:departureTime>late afternoon</p:departureTime> <p:seatPreference>aisle</p:seatPreference> </p:departure> </pi:itinerary>

  28. 5.3 Spanning Sources (cont'd) Original request • Below is the XQuery response, which looks up airport and flight information in a relational database: for $city in doc("incoming.xml")//p:departing let $airports := sql:table("airports")/AIRPORTS/row[CITY = $city] return if(count($airports) = 0) then <error>No airports found for {$city}!</error> else if(count($airports) = 1) then <airport>{string($airports/AIRPORT)}</airport> else if(count($airports) > 1) then <airportChoices> { for $c in $airports/AIRPORT return (string-value($c), " ") } </airportChoices> else() Relational DB

  29. 6. SQL/XML and XQuery: Are both needed? • SQL/XML is best for SQL programmers who think of their tasks in SQL terms and need to create results in XML • SQL/XML is like an SQL reporting tool, except that it creates reports in XML • XQuery is best for XML programmers who are working either with only XML data or with both XML and relational data • When XQuery needs to work with relational data, it needs an SQL/XML view of the relational data source; in this way, XQuery and SQL/XML work well together • SQL/XML works well in traditional SQL environments, provides full access to the standard SQL language, has APIs like JDBC and ODBC, and has implementations provided by Oracle and IBM • XQuery is a young language, with currently no standardized support for updates and full-text search and no complete API • The bottom line is that if you're working with only XML data as your input(s), use XQuery • If you're working with only relational data as your input(s), use SQL/XML • If you're working with both types of data as your input(s), use XQuery with SQL/XML views

More Related