Oracle and XML

1. Oracle and XML Mingzhu Wei 2005-2-27

2. Outline • Generation of XML • Storage of XML • XML transformations • XML schemas and mapping of documents • New features in Oracle 9i • New features in Oracle 10g

3. Oracle’s infrastructure support for XML • Object-Relational infrastructure • Can establish a direct mapping between XML document and object-relational instances • Oracle provide a set of services for XML(storage service, query service, index service, and so on) • Provide a native Java VM that is closely integrated with the database for high performance and scalability.

4. Generating XML • Oracle provides Built-in, native functions linked to the Oracle9i kernel for generating XML. • Three new, highly-scalable PL/SQL functions (SYS_XMLGEN, SYS_XMLAGG, and DBMS_XMLGEN) provide an alternative to the XDK for working within the database to generate XML.

5. Generation of XML • XML can be generated form object-relational tables and view. Product : XMLSQL Utility • XML SQL Utility converts the result of a SQL query into XML by mapping the query alias or column names into the element tag names and preserving the nesting of object types. • The XML SQL Utility for Java consists of a set of Java classes that: • Pass a query to the database and generate an XML document (text or DOM) from the results. • Write XML data to a database table.

6. Generation of XML cont. • Generating XML from Query Results

7. Generation of XML cont. • Example: Generating XML from Query Results • SELECT EMPNO, ENAME FROM EMP WHERE EMPNO = 7654; • <?xml version="1.0"?> • <ROWSET> • <ROW id="1"> • <EMPNO>7654</EMPNO> • <ENAME>MARTIN</ENAME> • </ROW> </ROWSET> • Note the format of the XML document. By default, ROWSET is the element name of the XML document element. ROW is the element name for each row in the query result. Data such as EMPNO and ENAME are also represented as elements nested within the ROW node. In general, data is represented as elements and attributes are used to constrain the data where needed. Should an application require a different set of tags, an XSL stylesheet can perform the transformation dynamically.

8. Storage of XML data in Oracle • Storage of “large objects” or LOBs as CLOB, BLOB, or externally stored ‘binary files’ (BFILE) • CLOBs can store large character data, can be useful for storing unstructured XML documents. • BFILES are external file references. XML is stored outside the RDBMS. But metadata for the document may be stored in object- relational tables for fast indexing and access. • Storing XML as object-relational instances.

9. Storage of XML in Oracle • XML data coming into the database maybe of two forms: structured documents and unstructured documents. • Storage of structured XML documents: • The documents can be stored in relational or object-relational structures. The object- relational type system can provide a direct mapping to the XML document. • Storage of unstructured XML documents • Each XML message in a queue might be of a different structure • Oracle 8i provides interMedia Text cartridge for indexing CLOB columns

10. Storage of XML in Oracle • Hybrid approach • It’s better if user can control the granularity of the mapping. • In the book example, user would like to query on top-level elements such as chapter, section, title. And the contents within a section can be stored in a CLOB.

11. XML transformations • Transformation of query results • the XML Parsers include an integrated XSL Transformation (XSLT) Processor for transforming XML data using XSL stylesheets. Using the XSLT processor, you can transform XML documents from XML to XML, HTML, or virtually any other text-based format.

12. XML schemas and mapping of documents • The XML Schema Processor parses and validates XML files, offering features and functionality beyond what is provided by DTDs. As defined by the W3C, "XML Schemas express shared vocabularies and allow machines to carry out rules made by people." • The Oracle XML Schema Processor supports the three parts of the W3C XML Schema Working Draft: • Part 0: Primer XML Schema • Part 1: Structures XML Schema • Part 2: Datatypes

13. XML schemas and mapping of documents • XML Schema are also at the core of the native XML support in the Oracle9i database, letting developers easily and seamlessly manipulate complex XML e-business data using Java and SQL, so Oracle customers benefit from the very latest open XML standards.

14. Areas of  XML support in Oracle9i • can store XML in the database via SQL and  render traditional database data as XML.  The areas of  XML support in Oracle9i include: • Built-in, native functions linked to the Oracle9i kernel for generating XML. Three new, highly-scalable PL/SQL functions (SYS_XMLGEN, SYS_XMLAGG, and DBMS_XMLGEN) provide an alternative to the XDK for working within the database to generate XML.

15. Areas of  XML support in Oracle9i • Native XML type support. For applications that need to store and retrieve large amounts of complex XML, Oracle9i stores XML natively by introducing XMLType, a new object datatype, and features extremely fast, navigational access and searches for XML documents. • The UriType family of types can store and query URI-refs in the database. SYS.UriType is an abstract object type that provides functions to access the data pointed to by the URL. SYS.HttpUriType and SYS.DBUriType are subtypes of UriType. The HttpUriType can store HTTP URLs and the DBUriType can store intra-database references.

16. Areas of  XML support in Oracle9i • New Oracle Text XML-searching capabilities including new operators HASPATH and INPATH that leverage XPath expressions within Oracle Text queries. You can use Oracle Text to perform searches on XML documents stored in Oracle9i by indexing the XML as plain text, or as document sections for more precise searches, such as find "Oracle WITHIN title" where "title" is a section of the document.

17. Areas of  XML support in Oracle9i • Oracle Advanced Queuing (AQ) uses XML to provide infrastructure for e-business integration. Specifically, AQ defines a presentation named iDAP (Internet Data Access Presentation) using XML. With iDAP, AQ operations can be executed  via Internet transport protocols such as HTTP(S), SMTP, and FTP. AQ supports XML datatype payloads and allows definitions of subscriptions based on contents of XML messages.

18. New features in Oracle 10g • Oracle Database 10g breaks new ground in support for XML technology, offering very rich features for importing, storing, querying, and generating XML data. Providing native, structured XML storage as well as support for unstructured document storage and shredding, Oracle Database 10g allows you to pull XML data from files and merge it with relational data in views. But before jumping into an upgrade for the enhanced XML capabilities, Oracle shops should note that most of the functionality is available in Database 9i.

19. New features in Oracle 10g Oracle Database can read and write to WebDAV (Web Distributed Authoring and Versioning) repositories, giving users a nice Web folder view of the XML data they can request. The WebDAV support allows administrators to set up access to thousands upon thousands of XML files without requiring much disk space. This is because these WebDAV files, though they appear to be regular XML files, are just shortcuts, queries that point back to the database. The data that comprise these files doesn't materialize until it's requested by opening the file or copying it to a different location. Along with WebDAV, Oracle also supports transferring XML documents across FTP and HTTP.

20. New features in Oracle 10g Perhaps the most important improvement in Database 10g is XML schema evolution. Schema evolution allows you to implement changes to XML schema by mapping the existing data to the new schema. Instead of having to export and re-import all of your XML data, you just create the XSLT (XSL Transformation) style sheet to transform your old documents to the new schema, and the database takes care of the rest. Schema evolution greatly eases the management of XML data, because one thing administrators can count on is constantly changing requirements. 

21. Thanks !