Abstract

While XML databases are a good solution for managing XML content, frequently it is necessary to uses non-XML databases for managing XML content. In most cases, these databases will be relational databases. There a two major approaches of how to manage XML content in a relational database. The first approach is to define a mapping between XML and relational structures and work with this mapping. The second approach is to use the XML-specific functionality, which is increasingly provided by relational databases, turning them into XML-aware databases.

XML is Trees

• XML documents are trees
  • applications may have different internal data models (mapped to trees for interfacing)
  • the exchange and processing of XML documents is tree-based
• Where and how is XML being used?
  • as a pure transfer syntax (Web Services very often are used like this)
  • as artifacts that have a longer lifespan (archiving of XML business documents)
  • as the applications data model (there is nothing but XML)
• XML usage results in very different requirements for XML tools
  • Web Service programmers often never see the tree
  • archived XML documents need to be searchable
  • XML-centric applications need to store XML efficiently

Storing XML

• XML documents are text files
  • they can be stored in file systems (they are self-describing)
  • they can be retrieved by searching through the file system
• File systems are not designed to store millions of documents
  • standard file system implementation usually slow down dramatically
  • standard procedures (backup/restore/concurrency) do not work well
• Problems with File Systems as XML Databases
  • the number of documents is too large
  • there is no structured access (XPath Shell (XPsh) provides an XML-find/grep)
  • there is no access optimization (XPsh is very slow)

Generic XML Storage

• Relational databases are the state of the art since 1976
  • this is long enough to build highly optimized and robust systems
  • this is long enough to have ER hard-wired into some brains
• XML is more powerful than ER
  • repetitions of elements do not map well
  • choices do not map well
  • ordered content does not map well
  • mixed content does not map well
• Storing XML in a relational database is hard
  • it can be done by piggybacking structural information as content
  • using the resulting structures is awkward and very inefficient

Tree Table

ID Type Name Value Parent Left 1 Root 2 Element a 1 3 Element b 2 4 Element c 2 3 5 Text Text 3 6 Attribute att 42 4

Why XML and Databases?

• XML is constantly getting more popular
  • XML as a document format was first used as wire format only
  • instead of parsing manually, parser interfaces provide better XML support
  • data binding frameworks bind XML even more tightly into applications
  • if all programs somehow hide the XML, why not work on XML directly?
• What is XML for an application?
  • an (increasingly popular) way to represent the data?
  • the data itself?
  • currently, the representation perspective is more popular
  • as XML is increasingly penetrating applications, this may change

XML Interchange

XML Support in DBMS

XML DBMS

Model Mapping

• Relational databases are not good tools for storing XML
  • they might be appropriate if the schema disallows problematic constructs
  • they often are already deployed and applications must live with them
• If the data model is ER-oriented, relational databases are good tools
  • XML may be invisible from the model point of view
  • parts of the model may be encoded as an XML schema
• If the XML is not visible in the model, it can be structurally inaccessible
  • e.g., a product catalog may contain product descriptions in XHTML rich text snippets
  • for managing the product catalog data, the XHTML is not relevant
• If the XML is part of the model, it should be accessible structurally
  • if the product catalog XHTML contains links to other products, these links are important
  • they could be extracted (creating redundant and hard to maintain data)
  • if they are hidden in the XHTML, all XHTML snippets have to be parsed
  • ideally, the database should be able to query the XHTML snippet

XML is Text

• XML documents can be stored as text
  • databases typically have various datatypes for text storage
  • if the database supports Unicode, any XML document can be stored
• The XML structure is completely invisible to the database
  • working with the XML requires querying and parsing the XML text
  • this kind of storage does not allow any querying of the XML content

XML → ∗LOB

XML as a Datatype

• SQL supports a wide variety of datatypes
  • typed values are better than untyped values (they enable type-specific operations)
  • XML can be regarded as just another data type
• Introducing a datatype lets the database recognize the data
  • XML data can be stored in some format (a persistent DOM)
  • databases can provide functionality avoiding parsing/serialization (DOM-based)

XML Datatype

Mapping XML to Models

• Model-relevant data must be mapped to the database structures
  • this assumes there is a ER-model which describes the database structure
  • mapping XML is easy by definition because the XML is ER-compliant
• Is the data accessed as table data?
  • if shredded data is only used to assemble it again, it is just performance overhead
  • if shredded data is accessed relationally, then shredding makes sense

Shredding (XML → Columns)

XML as First-Class Citizen

• The XML Datatype defines XML as a sub-concept of ER
  • the overall structure of the database is relational
  • attributes may be of type XML, which means storing trees in tables
• Tables are not the only way to see the world
  • XML trees are an alternative to tables, not a datatype
  • XML-centric applications should not be forced to use tables at all
• XML can be regarded as replacing the ER-concept altogether
  • the database simply stores XML documents
  • applications can store, query, update, and manage XML documents in the database

XML DBMS

RDBish XML

• XML schemas can be designed with databases in mind
  • avoid unbounded repetitions of elements
  • avoid choices
  • avoid ordered content
  • avoid mixed content
• Many XML schemas are designed RDBish for compatibility reasons
  • it was decided that the XML should enable an easy mapping to relational structures
  • the person designing the schema has a ER-structured brain
  • the schema has been generated from a relational database schema

Problematic XML

• XML in its full glory is too much for tables
  • XML has been developed as a document format
  • XML is about hierarchy (which intentionally have been left out of ER)
  • XML is about highly irregular structures
• XML often is said to have to flavors
  • data-oriented XML: regular data which can be easily mapped to tables
  • document-oriented XML: irregular structures which are hard to map to tables
  • real-world XML often is a bit of both (e.g., content and metadata)
• Hybrid approaches sometimes are a good solution
  • data-oriented can be shredded and stored in tables
  • the document-oriented rest is stored as one object (text or XML)

SQL/XML:2003

• SQL/XML provides XML Support in DBMSs
  • it introduces XML as a Datatype
  • it introduces a number of operations for generating XML from query results
  • it defines mappings to bridge both worlds (SQL and XML)
• SQL/XML does not change anything about the database model
  • data is still stored in tables only
  • a column of a table may use the XML type
  • queries may return results in XML rather than as SQL result sets

SQL/XML Example

SELECT
  e.EmpId,
  e.FirstName,
  e.LastName,
  e.StartDate,
  e.EndDate
FROM Employees e WHERE e.EmpId = 12

SELECT
  XMLELEMENT (NAME "employee",
    XMLATTRIBUTES(e.EmpId as "id"),
    XMLELEMENT(NAME "names",
    XMLELEMENT(NAME "first", e.FirstName),
    XMLELEMENT(NAME "last", e.LastName)),
    XMLELEMENT(NAME "hire-dates",
      XMLATTRIBUTES(e.StartDate as "start", e.EndDate as "end")))
FROM Employees e WHERE e.EmpId = 12

SQL/XML:2007

• Adds the concept of XML Tables
• XML Tables are not tables, they are containers for XML
• SQL/XML:2007 changes the database's data model
  • it is now possible to have a database with no tables
  • likely use cases are to have both: traditional and XML tables
• SQL/XML:2007 defines a hybrid database: relational and XML database

Tables and Trees don't Mix

• Tables and trees are different data models
• Different technologies are used to handle these different models
• Think before choosing the wrong tool

Database Technologies do Mix

• Relational databases are good tools for regular data
• XML databases are good tools for document-oriented XML
• SQL/XML:2007 defines a database that does both
• Applications can choose the best mix of tables and trees