XML and Databases

XML Foundations [./]
Fall 2008 — INFO 242 (CCN 42572)

Erik WildeUC Berkeley School of Information, UC Berkeley School of Information
2008-12-04

[http://creativecommons.org/licenses/by/3.0/]

This work is licensed under a CC
Attribution 3.0 Unported License [http://creativecommons.org/licenses/by/3.0/]

Contents E. Wilde: XML and Databases

Contents

2Abstract
3XML is Trees
4Storing XML
1 Relational Databases
- 6Generic XML Storage
- 7Tree Table
2 Database Support for XML
- 9Why XML and Databases?
- 10XML Interchange
- 11XML Support in DBMS
- 12XML DBMS
3 XML Storage in Databases
- 14Model Mapping
- 15XML is Text
- 16XML → ∗LOB
- 17XML as a Datatype
- 18XML Datatype
- 19Mapping XML to Models
- 20Shredding (XML → Columns)
- 21XML as First-Class Citizen
- 22XML DBMS
4 XML in Relational Databases
- 24RDBish XML
- 25Problematic XML
- 4.1 SQL/XML
  - 27SQL/XML:2003
  - 28SQL/XML Example
  - 29SQL/XML:2007
5 XML Databases
- 5.1 Files vs. Databases
  - 32Abstraction Layers
  - 33File-Based XQuery
  - 34Database-Based XQuery
  - 35XDBMS Database Management
- 5.2 Updating XDBMS
  - 37XQuery
  - 38XUpdate
  - 39XQuery Update Facility
6 Conclusions
- 41Tables and Trees don't Mix
- 42Database Technologies do Mix
- 43XML Databases for XML Scenarios

E. Wilde: XML and Databases

(2) 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.

E. Wilde: XML and Databases

(3) 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

E. Wilde: XML and Databases

(4) 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) [http://sourceforge.net/projects/xpsh] provides an XML-find/grep)
- there is no access optimization (XPsh is very slow)

Relational Databases

Outline (Relational Databases)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

Relational Databases E. Wilde: XML and Databases

(6) 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

Relational Databases E. Wilde: XML and Databases

(7) 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

Database Support for XML

Outline (Database Support for XML)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

Database Support for XML E. Wilde: XML and Databases

(9) 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

Database Support for XML E. Wilde: XML and Databases

(10) XML Interchange

Database Support for XML E. Wilde: XML and Databases

(11) XML Support in DBMS

Database Support for XML E. Wilde: XML and Databases

(12) XML DBMS

XML Storage in Databases

Outline (XML Storage in Databases)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

XML Storage in Databases E. Wilde: XML and Databases

(14) 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 Storage in Databases E. Wilde: XML and Databases

(15) 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 Storage in Databases E. Wilde: XML and Databases

(16) XML → ∗LOB

XML Storage in Databases E. Wilde: XML and Databases

(17) 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 Storage in Databases E. Wilde: XML and Databases

(18) XML Datatype

XML Storage in Databases E. Wilde: XML and Databases

(19) 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

XML Storage in Databases E. Wilde: XML and Databases

(20) Shredding (XML → Columns)

XML Storage in Databases E. Wilde: XML and Databases

(21) XML as First-Class Citizen

The XML Datatype [XML Datatype (1)] 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 Storage in Databases E. Wilde: XML and Databases

(22) XML DBMS

XML in Relational Databases

Outline (XML in Relational Databases)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

XML in Relational Databases E. Wilde: XML and Databases

(24) 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

XML in Relational Databases E. Wilde: XML and Databases

(25) 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

Outline (SQL/XML)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

SQL/XML E. Wilde: XML and Databases

(27) SQL/XML:2003

SQL/XML provides XML Support in DBMS [XML Support in DBMS (1)]s
- it introduces XML as a Datatype [XML as a Datatype (1)]
- 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 E. Wilde: XML and Databases

(28) 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 E. Wilde: XML and Databases

(29) 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

XML Databases

Outline (XML Databases)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

Files vs. Databases

Outline (Files vs. Databases)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

Files vs. Databases E. Wilde: XML and Databases

(32) Abstraction Layers

Files systems are general-purpose mechanisms for managing data
- files may contain any data that can be encoded as a sequence of bytes
- file systems maintain some metadata about files (owner, dates, permissions)
- data management is limited to reading or writing streams of bytes
Databases are specialized tools for managing data
- they prescribe a logical model which defines the type of data to work with
- they provide operations on this logical model only (and not on the physical model)
- the physical model can be optimized to provide better performance/security/reliability
- the physical model can be stored in files or as raw data without a file system
Relational databases (RDBMS) use tables as their logical model
XML databases (XDBMS) use XDM [XQuery 1.0 and XPath 2.0 Data Model (XDM)] (typed Infosets) as their document model

Files vs. Databases E. Wilde: XML and Databases

(33) File-Based XQuery

File-based XQuery Processing

Files vs. Databases E. Wilde: XML and Databases

(34) Database-Based XQuery

DB-based XQuery Processing

Files vs. Databases E. Wilde: XML and Databases

(35) XDBMS Database Management

Databases are optimized data management systems
- data must be structured according to the Data Definition Language (DDL)
- it can only be manipulated using the Data Manipulation Language (DML)
- DDL and DML allow databases to implement optimized storage and retrieval
XML is a new DDL, and relational databases cannot handle XML natively
- relational databases try to adapt to the new world of XML data []
- XML databases must implement their own optimized storage and retrieval
XML documents do not have to be stored as text-based XML document files
- XML is the data model an application expects when working with XML
- XML storage can be optimized for various purposes, one example is Persistent DOM (PDOM)
- database data structures always depend on the expected write vs. read ratio

Updating XDBMS

Outline (Updating XDBMS)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

Updating XDBMS E. Wilde: XML and Databases

(37) XQuery

XQuery [XML Query (XQuery) – Part I] is a read-only language
- queries a collection of XML documents (XDM [XQuery 1.0 and XPath 2.0 Data Model (XDM)] instances)
- returns an XDM instance, serialized as XML or something else
Updating XML databases currently is not covered by a widely accepted standard
- XUpdate [XUpdate (1)] is a simple and rather old solution (04/2000)
- various XQuery Update Facility [XQuery Update Facility (1)] have been proposed for XQuery
- XML database implementers often introduce proprietary update facilities
- the W3C is working on an XQuery Update Facility [http://www.w3.org/TR/xquery-update-10/]

Updating XDBMS E. Wilde: XML and Databases

(38) XUpdate

XUpdate [http://xmldb-org.sourceforge.net/xupdate/xupdate-wd.html] defines an language for specifying XML updates
- the data model is based on XPath 1.0
- the syntax is based on XML

XUpdate has no connections with a query language, it is for updates only

<addresses version="1.0">
  <address id="1"> 
    <fullname>Andreas Laux</fullname> 
    <born day='1' month='12' year='1978'/> 
  </address> 
</addresses>

<xupdate:modifications version="1.0" xmlns:xupdate="http://www.xmldb.org/xupdate"> 
  <xupdate:insert-after select="/addresses/address[1]" > 
    <xupdate:element name="address">
      <xupdate:attribute name="id">2</xupdate:attribute>
        <fullname>Lars Martin</fullname> 
        <born day='2' month='12' year='1974'/> 
      </xupdate:element> 
    </xupdate:insert-after> 
</xupdate:modifications>

Updating XDBMS E. Wilde: XML and Databases

(39) XQuery Update Facility

XQuery 1.0 [XML Query (XQuery) – Part I] had been planned to be a read-only language
- creating a fully functional language would have been to ambitious
- with a solid formal foundation, XQuery can be upgraded to also provide update features
Several XQuery update extensions have been proposed
- updating goes through a consolidation phase similar to querying
- the eventual XQuery update facility will be integrated with XPath
W3C's XQuery Update Facility [http://www.w3.org/TR/xqupdate/] is in early draft status

do insert <year>2005</year> after fn:doc("bib.xml")/books/book[1]/publisher

do delete fn:doc("bib.xml")/books/book[1]/author[last()]

do replace fn:doc("bib.xml")/books/book[1]/publisher with fn:doc("bib.xml")/books/book[2]/publisher

Conclusions

Outline (Conclusions)

Relational Databases [2]
Database Support for XML [4]
XML Storage in Databases [9]
XML in Relational Databases [5]
1. SQL/XML [3]
XML Databases [7]
1. Files vs. Databases [4]
2. Updating XDBMS [3]
Conclusions [3]

Conclusions E. Wilde: XML and Databases

(41) 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

Conclusions E. Wilde: XML and Databases

(42) 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

Conclusions E. Wilde: XML and Databases

(43) XML Databases for XML Scenarios

XDBMS are useful for managing large document collections
Managing a DBMS requires a minimum amount of expertise
Performance benefits can be tremendous
XDBMS are still in their early years, expect some surprises …

2008-12-04 XML Foundations [./]
Fall 2008 — INFO 242 (CCN 42572)