XMLII

1. XMLII • XSchema • XQuery • Oracle XSU

2. XML Schema • XML Schema is a more sophisticated schema language which addresses the drawbacks of DTDs. Supports • Typing of values • E.g. integer, string, etc • Also, constraints on min/max values • User-defined, complex types • Many more features, including • uniqueness and foreign key constraints, inheritance • XML Schema is itself specified in XML syntax, unlike DTDs • More-standard representation, but verbose • XML Scheme is integrated with namespaces • BUT: XML Schema is significantly more complicated than DTDs.

3. <xs:schema xmlns:xs=http://www.w3.org/2001/XMLSchema> <xs:element name=“bank” type=“BankType”/> <xs:element name=“account”><xs:complexType> <xs:sequence> <xs:element name=“account_number” type=“xs:string”/> <xs:element name=“branch_name” type=“xs:string”/> <xs:element name=“balance” type=“xs:decimal”/> </xs:squence></xs:complexType> </xs:element> ….. definitions of customer and depositor …. <xs:complexType name=“BankType”><xs:squence> <xs:element ref=“account” minOccurs=“0” maxOccurs=“unbounded”/> <xs:element ref=“customer” minOccurs=“0” maxOccurs=“unbounded”/> <xs:element ref=“depositor” minOccurs=“0” maxOccurs=“unbounded”/> </xs:sequence> </xs:complexType> </xs:schema> XML Schema Version of Bank DTD

4. XML Schema Version of Bank DTD • Choice of “xs:” was ours -- any other namespace prefix could be chosen • Element “bank” has type “BankType”, which is defined separately • xs:complexType is used later to create the named complex type “BankType” • Element “account” has its type defined in-line

5. More features of XML Schema • Attributes specified by xs:attribute tag: • <xs:attribute name = “account_number”/> • adding the attribute use = “required” means value must be specified

6. Querying and Transforming XML Data • Translation of information from one XML schema to another • Querying on XML data • Above two are closely related, and handled by the same tools • Standard XML querying/translation languages • XPath • Simple language consisting of path expressions • XSLT • Simple language designed for translation from XML to XML and XML to HTML • XQuery • An XML query language with a rich set of features

7. XQuery • XQuery is a general purpose query language for XML data • Standardized by the World Wide Web Consortium (W3C) • XQuery is derived from the Quilt query language, which itself borrows from SQL, XQL and XML-QL • XQuery uses a for … let … where … order by …result … syntaxfor SQL fromwhere  SQL whereorder by  SQL order by result  SQL selectlet allows temporary variables, and has no equivalent in SQL

8. For clause uses XPath expressions, and variable in for clause ranges over values in the set returned by XPath Simple FLWOR expression in XQuery find all accounts with balance > 400, with each result enclosed in an <account_number> .. </account_number> tagfor $x in /bank-1/customer/account let $acctno := $x/account_number/text() where $x/balance > 400 return <account_number> { $acctno } </account_number> Items in the return clause are XML text unless enclosed in {}, in which case they are evaluated Let clause not really needed in this query, and selection can be done In XPath. Query can be written as: for $x in /bank-1/customer/account[balance>400] return { $x/account_number } FLWOR Syntax in XQuery

9. Joins • Joins are specified in a manner very similar to SQLfor $a in /bank/account, $c in/bank/customer, $d in /bank/depositor where $a/account_number = $d/account_number and $c/customer_name = $d/customer_name return <cust_acct> { $c $a } </cust_acct> • The same query can be expressed with the selections specified as XPath selections: for $a in /bank/account $c in /bank/customer $d in /bank/depositor[ account_number = $a/account_number and customer_name = $c/customer_name] return <cust_acct> { $c $a } </cust_acct>

10. The following query converts data from the flat structure for bank information into the nested structure used in bank-1 <bank-1> { for $c in /bank/customer return <customer> { $c/* } { for $d in /bank/depositor[customer_name = $c/customer_name], $a in /bank/account[account_number=$d/account_number] return $a } </customer> } </bank-1> $c/* denotes all the children of the node to which $c is bound, without the enclosing top-level tag $c/text() gives text content of an element without any subelements / tags Nested Queries

11. The order by clause can be used at the end of any expression. E.g. to return customers sorted by namefor $c in /bank/customerorder by $c/customer_name return <customer> { $c/* } </customer> Use order by $c/customer_name to sort in descending order Can sort at multiple levels of nesting (sort by customer_name, and by account_number within each customer) <bank-1> {for $c in /bank/customer order by $c/customer_name return <customer> { $c/* } { for $d in /bank/depositor[customer_name=$c/customer_name], $a in /bank/account[account_number=$d/account_number] } order by $a/account_number return <account> $a/* </account> </customer> } </bank-1> Sorting in XQuery

12. Oracle XML-SQL Utility • Transform data from o-r tables/views into XML • Extract relevant data from an XML document • Insert data into database tables • Using a canonical mapping • Available as • Java command-line front end • Java API • PL/SQL API

13. SQL-to-XML Mapping • For each SQL query Q, will get the XML file with the following DTD • <!DOCTYPE ROWSET[ <!ELEMENT ROWSET (ROW*)> <!ELEMENT ROW (attribute-list returned by Q)> ]> • If some attribute is of nested table type, then the element will have sub-element corresponding to the attributes of the nested table

14. Example of SQL-to-XML • We first create a table country • CREATE TABLE country (name char(50), region char(60), area decimal(10), population decimal(11), gdp decimal(14), primary key (name) );

15. Example of SQL-to-XML CREATE TYPE CountriesType AS OBJECT ( country_name char(50), area decimal(10), population decimal(11), gdp decimal(14) ); / CREATE TYPE CountriesTableType AS TABLE OF CountriesType; / CREATE TABLE Regions ( region_name char(60), countries CountriesTableType, area decimal(10), population decimal(11), gdp decimal(14) ) NESTED TABLE Countries STORE AS CountriesTable;

16. Example of SQL-to-XML INSERT INTO Regions(region_name,area,population,gdp) SELECT region, SUM(area), SUM(population), SUM(gdp) FROM country GROUP BY region; UPDATE Regions SET countries = CAST(MULTISET( SELECT * FROM country WHERE region = Regions.region_name) AS CountriesTableType );

17. Example of SQL-to-XML • Root – ROWSET element • Each row – ROW element • Each simple attribute – sub-element of ROW element • Each nested table attribute – sub-element with sub-element • Each ROW element has a num attribute • Null values are left out

18. Java front-end utility - OracleXML Export CLASSPATH=/usr/local/oracle/software/jdbc/lib/classes12.zip:/usr/local/oracle/software/rdbms/jlib/xsu11.jar:. java OracleXML getXML \ -user “username/passwd”\ -conn “jdbc:oracle:thin:@erg.csci.unt.edu:1521:ERG”\ “select * from country” • OracleXML parameters • User name and password • JDBC connection string • SQL query • Extract data from the database to XML form

19. A Few More Options java OracleXML getXML -user ‘user/passwd' -conn 'jdbc:oracle:thin:@erg.csci.unt.edu:1521:ERG' \ -withDTD -rowsetTag 'countries' -rowTag 'country' -rowIdAttr 'countryID' \ 'select name as "@countryname", gdp, area from country‘ • -withDTD: generate the DTD • -rowsetTag: specify the rowsetTag name • -rowTag: specifiy the rowTag name • -rowIdAttr: specify the name of the row ID attribute of each row • name as “@countryname” rename the name sub-element tag to “countryname” tag

20. XSU Java API • Through Java API • Allow generation of XML data • Allow insertion of data from an XML document • Allow delete and updates based on XML document

21. API classes • Oracle.xml.sql.query.OracleXMLQuery (for query) • Create a JDBC Connection • Create an OracelXMLQUery instance • Set options in the OracleXMLQuery object • Obtain the results • Oracle.xml.sql.dml.OracleXMLSave (for update, insert, and delete) • Create a JDBC Connection • Creat and OracleXMLSave object • Set options in the OracleXMLSave ojbect • Invoke the proper method to insert, delete, or update

22. Extracting XML import oracle.jdbc.driver.*; import oracle.xml.sql.query.OracleXMLQuery; import java.lang.*; import java.sql.*; public class xsuGet { public static void main(String[] argv) throws SQLException { try { Class.forName ("oracle.jdbc.driver.OracleDriver"); } catch (ClassNotFoundException e) { System.out.println ("Could not load the driver"); return; } Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@erg.csci.unt.edu:1521:ERG", “username",“passwd"); // Create the query class. OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from regions"); // Get the XML string String str = qry.getXMLString(); // Print the XML output System.out.println(" The XML output is:\n"+str); qry.close(); conn.close(); } }

23. XMLType • A system defined object • With built in member functions • Create, extract, and index XML data • Can be used as column type

24. Define a table with XMLType CREATE TABLE addrbook( name varchar(20), card SYS.XMLTYPE, creationDate Date ); • Address book has a name, a creationData, and a XMLType attribute to contain the contact information structured in XML format

25. XMLType Insertion • Use member function createXML(‘xml data’); Example: insert into addrbook values ('Roger', sys.XMLType.createXML( '<ACARD CREATEDBY="raj"> <EMAIL>roger12@yahoo.com</EMAIL> <WPHONE>111-5678</WPHONE> <ADDRESS> <LINE1>123 Main Street</LINE1> <CITY>Atlanta</CITY> <STATE>GA</STATE> <ZIP>33333</ZIP> </ADDRESS> </ACARD>'), sysdate); Non-well-formed XML will be rejected

26. Querying XML Data • Use member function: • getClobVal CLOB (Character Large Object) • getStringVal • GetNumberVal • Extract(‘Xpath expression’) • Example: select a.card.extract('/ACARD').getstringVal() from addrbook a where name = 'Roger';