XML and Databases

XML Foundations (INFO 242)

Erik Wilde, UC Berkeley School of Information
2007-12-04
Creative Commons License

This work is licensed under a CC
Attribution 3.0 Unported License

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

Storing XML

Outline (Relational Databases)

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

Generic XML Storage

Tree Table

tree-table.png
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

Outline (Database Support for XML)

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

Why XML and Databases?

XML Interchange

xml-dbms-application.png

XML Support in DBMS

xml-dbms-xmlsupport.png

XML DBMS

xml-dbms-xdbms.png

Outline (XML Storage in Databases)

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

Model Mapping

XML is Text

XML → ∗LOB

xml-storage-lob.png

XML as a Datatype

XML Datatype

xml-storage-datatype.png

Mapping XML to Models

Shredding (XML → Columns)

xml-storage-shredding.png

XML as First-Class Citizen

XML DBMS

xml-storage-xdbms.png

Outline (XML in Relational Databases)

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

RDBish XML

Problematic XML

Outline (SQL/XML)

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

SQL/XML:2003

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

Outline (Conclusions)

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

Tables and Trees don't Mix

Database Technologies do Mix