1 / 62

<ORACLE XML DB>

<ORACLE XML DB>. <Author fullName=‘Marouane Bouzoubaa’/> <Course title=‘XML & Data Management ‘/> <Supervisor fullName=‘Dr. Hachim Haddouti’/>. </ORACLE XML DB>. Outline. Why XML in the Database What is XML DB? XML DB Architecture The XMLType Datatype Example1:

lucita
Download Presentation

<ORACLE XML DB>

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. <ORACLE XML DB> <Author fullName=‘Marouane Bouzoubaa’/> <Course title=‘XML & Data Management ‘/> <Supervisor fullName=‘Dr. Hachim Haddouti’/> </ORACLE XML DB>

  2. Outline • Why XML in the Database • What is XML DB? • XML DB Architecture • The XMLType Datatype • Example1: • Schemas Registration • Inserting and Querying Data • Creating and Using Indices • The XML DB Repository • The XML DB APIs • XML Future Additions • Summary

  3. Common XML Architectures

  4. Why XML in the Database? • Reduce maintenance costs of extra moving parts; i.e. XML middleware. • Eliminate separate ‘XML-processing’ layers • Keep applications standards-based • W3C, IETF, ANSI/ISO, J2EE • Eliminate proprietary file formats, message formats, delimited columns • Enable richer semantics and better management for content-oriented applications • Store as XML vs. Files or LOBs • Queryability

  5. Oracle and XML • Oracle has been at the forefront of XML technology for quite a while. • From the beginning Oracle implemented the various different XML standards, which have largely been defined by the W3C. • Oracle XML parser: SAX and DOM APIs. • XSLT transformations and support for the initial XML Schema proposals • Etc… ahead of time.

  6. ORACLE XML DB Features • With Oracle XML DB, you get all the advantages of relational database technology and XML technology at the same time. • Oracle XML DB provides an independent infrastructure to store and manage XML data: • storage-independent • content-independent • programming language-independent

  7. ORACLE XML DB • Oracle XML DB is the term used to describe the technology in the Oracle 9i Release 2 database for supporting XML • Extends the popular Oracle relational database • Keeps all the functionality of RDBMS • Provides all of the functionality associated with a native XML database • Delivers high-performance storage and retrieval of XML.

  8. XML Support in Oracle 9i R2 • XMLType • XML Repository

  9. XML DB Architecture - Data View

  10. XML DB Architecture - Content View

  11. The XMLType Datatype Definition • “XMLType is a native data-type that is used to store and manage XML documents in columns or tables”

  12. The XMLType Datatype • Introduced with Oracle 9i Rel. 1 to store XML data in the database • Enhanced extensively in 9i Rel. 2 • XMLType is a native server data-type • allows the database to understand that a column or table contains XML • same way as DATE data-type

  13. The XMLType Datatype • XMLType is an object type • In tables: xmltype can either be used as data-type for a column or an entire table create table orders ( orderNo NUMBER(10), customerId NUMBER(8), xmlorder XMLTYPE, date_entered DATE ) -------------------------------------- create table orders of XMLTYPE

  14. The XMLType Datatype • XMLType is an object type • In PL/SQL: a parameter, a return value, a PL/SQL table, a record or a variable can be declared as an XMLTYPE

  15. The XMLType Datatype • The XMLType data type can also be used when defining views: • XMLType view • relational view that includes an XMLType column • This allows to expose content stored in relational tables and external data sources as XML documents.

  16. The XMLType Datatype • XMLType has a number of built-in member functions for creating, extracting and indexing XML data • Allows access to the XML data using standard XPath expressions • A new 9.0.2 operator, XMLTransform, allows for XSL transformations • The XML data can optionally be based on a W3C schema

  17. The XMLType Member Functions XML DB provides a number of functions for querying and updating XML data. The most important of these functions are: • extract(xmltype,xpath) extracts a subset of nodes, or an XML fragment, from an XML document • existsNode(xmltype,xpath) checks whether or not a particular node exists in the XMLType, it is often found as part of a WHERE clause statement • extractValue(xmltype,xpath) extracts the value of a single node in an XML document using an XPath expression • updateXML() updates an entire XML document or a portion of the XML document in the database

  18. The XMLType Member Functions • schemaValidate() Validates the contents of the XMLType against an XML Schema • transform() XSL Transformation • getClobVal() Returns the document as a CLOB. • getNumVal() Returns the fragment or text value in the XMLType to a number. • getStringVal() Returns the document as a string.

  19. XMLType Data Storage • XMLType data can be stored in one of two ways: • In CLOB storage to maintain the original XML content • In Structured storage (tables and views) to maintain DOM fidelity

  20. XMLType Data Storage • When you create an XMLType column without an XML schema, a hidden CLOB column is automatically created • The hidden column stores the extra non-SQL based information

  21. CLOB VS. Structured Storage

  22. XML Piecewise Update • When using Structured Storage: • You can use XPath to specify individual elements and attributes of your document to update, without rewriting the entire document. • This is more efficient, especially for large XML documents.

  23. XMLType Data Storage • Constraining a column or table to an XML Schema has a number of advantages: • The database will store only valid XML documents • Oracle XML DB can use the information contained in the XML Schema to provide more intelligent query and update processing of the XML. • One important point to note: DTDs are not supported.

  24. XMLType: Virtual DOM • DOM problem: to operate on a document, the whole document has to be an object model in memory. • With large documents, that can be a serious problem. • Virtual DOM: however, allows users to materialize a DOM on the fly while processing requests (a "virtual" or "lazy" DOM), rather than doing it all at once; it only loads the data trees as they are requested, throwing away previously-referenced sections of the document if memory usage grows too large. This feature is completely transparent.

  25. XMLType: Schema Validation • Oracle XML DB does not automatically perform a full schema validation of documents as they are inserted into the database. • Why? • Schema validation is a fairly CPU intensive operation. • The development team decided to make full schema validation optional. • Full schema validation can be enabled on a Schema by Schema basis using a Check Constraint or Trigger.

  26. XMLType: Schema Validation • By default, Oracle XML DB performs a light weight validation of each document. It checks that mandatory elements and attributes are present, and that number of elements within a collection is compliant with the values defined in the XMLSchema • It does not check pattern matches, minimum lengths etc. This is a performance optimization.

  27. XMLType Datatype Example

  28. Example: Scenario • A mediator Company that offers many products from different suppliers • The client buys from different suppliers

  29. Example: Steps To Accomplish • 1. Register the XML schema with XML DB • 2. Create a relational table to hold both relational data and XML documents • 3. Load an XML document into the table • 4. Write an SQL query to retrieve • Whole Document • Data from both the relational fields and the XML document in the same query 5. Update an XML field 6. Create Indexes

  30. Example: Schema Filehttp://stud.alakhawayn.ma/~991BE740226/schema/order.xsd

  31. <?xml version="1.0" encoding="UTF-8"?> • <!-- edited with XMLSPY v5 rel. 3 U (http://www.xmlspy.com) by Marouane Bouzoubaa (AUI) --> • <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> • <xs:element name="order"> • <xs:complexType> • <xs:sequence> • <xs:element ref="fillByDate" minOccurs="0"/> • <xs:element name="items" type="itemsType"/> • </xs:sequence> • <xs:attribute name="orderNo" type="xs:positiveInteger"/> • </xs:complexType> • </xs:element> • <xs:complexType name="itemsType"> • <xs:sequence> • <xs:element name="item" type="itemType" maxOccurs="unbounded"/> • </xs:sequence> • </xs:complexType> • <xs:complexType name="itemType"> • <xs:sequence> • <xs:element ref="supplier"/> • <xs:element ref="itemNo"/> • <xs:element ref="unitCost"/> • <xs:element ref="qty"/> • </xs:sequence> • </xs:complexType> • <xs:element name="qty" type="xs:positiveInteger"/> • <xs:element name="supplier" type="xs:string"/> • <xs:element name="fillByDate" type="xs:date"/> • <xs:element name="itemNo" type="xs:string"/> • <xs:element name="unitCost"> • <xs:simpleType> • <xs:restriction base="xs:decimal"> • <xs:minInclusive value="0"/> • </xs:restriction> • </xs:simpleType> • </xs:element> • </xs:schema>

  32. Example: XML Filehttp://stud.alakhawayn.ma/~991BE740226/schema/order1.xml

  33. <?xml version="1.0" encoding="UTF-8"?> • <!-- edited with XMLSPY v5 rel. 3 U (http://www.xmlspy.com) by Marouane Bouzoubaa (AUI) --> • <order orderNo="104"> • <fillByDate>2003-08-28</fillByDate> • <items> • <item> • <supplier>Miloud Company</supplier> • <itemNo>SL35-721</itemNo> • <unitCost>17.97</unitCost> • <qty>12</qty> • </item> • <item> • <supplier>Spezi Company</supplier> • <itemNo>CP01-487</itemNo> • <unitCost>162.75</unitCost> • <qty>5</qty> • </item> • <item> • <supplier>Kamal BO Company</supplier> • <itemNo>ML32-42</itemNo> • <unitCost>12.25</unitCost> • <qty>5</qty> • </item> • <item> • <supplier>Al-Watania Company</supplier> • <itemNo>KP96-351</itemNo> • <unitCost>16.70</unitCost> • <qty>5</qty> • </item> • <item> • <supplier>Bouchta Company</supplier> • <itemNo>CT01-365</itemNo> • <unitCost>62.05</unitCost> • <qty>5</qty> • </item> • </items> • </ order>

  34. Registering a Schema • Before using an XML schema, it must be registered with the database • This is accomplished using the DBMS_XMLSCHEMA package • The two main functions are registerSchema and deleteSchema

  35. Registering an XML Schema exec DBMS_XMLSCHEMA.registerSchema( schema URL, schema source document) • The XML schema source document can be define as a VARCHAR, a CLOB, an XMLType or a URIType • Registering an XML schema adds a resource to the XML DB repository • A Schema can be registered locally or globally

  36. Registering an XML Schema • By default, schemas are loaded locally and associated with the “database schema” they are created under • BEGIN • DBMS_XMLSCHEMA.REGISTERSCHEMA( • schemaurl=>'http://stud.alakhawayn.ma/~991BE740226/schema/order.xsd', schemadoc=>sys.UriFactory.getUri('http://stud.alakhawayn.ma/~991BE740226/schema/order.xsd')); • END; • /

  37. Creating a Relational Table with XML Data • create table orders( • orderNo NUMBER(10), • customerId NUMBER(8), • xmlOrder XMLTYPE, • date_entered DATE) • XMLTYPE COLUMN xmlOrder • STORE ASCLOB • STORAGE (INITIAL 4096 NEXT 4096) • XMLSCHEMA “http://stud.alakhawayn.ma/~991BE740226/schema/order.xsd” ELEMENT “order” • ;

  38. Inserting, Querying and Updating • XML data is inserted into an XMLType column using the XMLType() constructor INSERT INTO ORDERS (orderNo,customerId, xmlOrder, date_entered) VALUES (orderNoSeq.nextval, 6135, XMLTYPE(‘ • <order orderNo="104" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://stud.alakhawayn.ma/~991BE740226/schema/order.xsd"> • <fillByDate>2003-08-28</fillByDate> • <items> • <item> • <supplier>Miloud Company</supplier> • <itemNo>SL35-721</itemNo> • <unitCost>17.97</unitCost> • <qty>12</qty> • </item> • <item> • <supplier>Spezi Company</supplier> • <itemNo>CP01-487</itemNo> • <unitCost>162.75</unitCost> • <qty>5</qty> • </item> • <item> • <supplier>SOMAPHA Company</supplier> • <itemNo>ML32-42</itemNo> • <unitCost>12.25</unitCost> • <qty>5</qty> • </item> • <item> • <supplier>Al-Watania Company</supplier> • <itemNo>KP96-351</itemNo> • <unitCost>16.70</unitCost> • <qty>5</qty> • </item> • <item> • <supplier>Bouchta Company</supplier> • <itemNo>CT01-365</itemNo> • <unitCost>62.05</unitCost> • <qty>5</qty> • </item> • </items> • </order> '), SYSDATE)

  39. Inserting, Querying and Updating • The XML document can be retrieved in a query using getClobVal() • select o.xmlOrder.getClobVal() from ORDERS o WHERE o.xmlOrder.existsNode('/order/items')= 1 • select extract(o.xmlOrder,’/’) from ORDERS o WHERE o.xmlOrder.existsNode('/order/items')= 1

  40. Hint • In order to display many lines of the XML result in the sql*plus: • set long 10000 • Set pagesize 10000

  41. Inserting, Querying and Updating • You can even combine both types of access into a single SQL query SELECT orderNo, customerId, extractValue(xmlOrder, '/order/fillByDate') FillByDate, date_entered FROM orders

  42. Inserting, Querying and Updating • The XML document can be updated using • update orders SET xmlOrder = updateXML(xmlOrder, '/order/items/item[2]/qty/text()',10);

  43. Creating XMLType Indices • Indices can be created on XMLType columns • These indices can either be function-based using extract() or existNodes(). Latter is better for bit-mapped indexes

  44. Creating XMLType Indices • CREATE INDEX FillBy_NDX ON ORDERS (xmlOrder.extract('/order/fillByDate'). getStringVal()); • CREATE BITMAP INDEX MultItems_NDX ON ORDERS (xmlOrder.existsNode('/order/items/ item/supplier'));

  45. Creating Context Indexes • Oracle ConText indexes can be defined for XMLTypes columns or tables CREATE INDEX co_ctx_ndx ON orders(xmlOrder) indextype is ctxsys.context; • Operation such as CONTAINS can then be used

  46. The XMLType/XML DB APIs • XMLType Member Functions • SYS_XMLGEN, SYS_XMLAGG and DBMX_XMLGEN • SQL/XML Functions • PL/SQL DOM API (DBMS_XMLDOM) • PL/SQL Parser API (DBMS_XML_PARSER) • XSLT Processor (DBMS_XSL_ PROCESSOR) • DBMS_XDBT (Context indexes) • The XML DB Repository views

  47. XML DB Repository

  48. XML DB Repository • It is possible to have your cake and eat it too • You can store your document as a Native XML Type in Oracle's XML DB repository, which will preserve byte-by-byte document fidelity and also shred it into SQL tables.

More Related