XML Databases

(1)

XML Databases

Silke Eckstein Andreas Kupfer

Institut für Informationssysteme

Technische Universität Braunschweig http://www.ifis.cs.tu-bs.de

6. SQL/XML

(2)

6.1Introduction

6.2 Publishing relational data in XML 6.3 XML data type

6.4 Queries

6. SQL/XML

6.4 Queries 6.5 Validation

6.6 SQL/XML standard 6.7 Overview

6.8 References

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig 2

(3)

• Creating XML documents from a database

– Introduced in the last chapter

– On a more or less conceptual level

• Not handled so far

6.1 Introduction

• Not handled so far

– Creating XML documents inside a database – Retrieving data from XML documents

– Changing XML document content

• Solution: Integration in database

– SQL/XML

(4)

• SQL/XML

– Storage of XML in all big commercial DBMS available – Proprietary solution for embedding in SQL

– SQL/XML = Part 14 of the SQL-Standard: XML

6.1 Introduction

SQL/XML = Part 14 of the SQL-Standard: XML functionality

– Incorporates the corresponding standards for XML (XML Schema, XQuery)

– Basic idea:

• Mapping of SQL concepts to XML (see last chapter)

• Own datatype to store XML

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig 4 [Kud07]

(5)

• SQL/XML

– Datatype XML with

6.1 Introduction

<City>

<Name>

Braunschweig

</Name>

<Zip>38100</Zip>

<Zip>38106</Zip>

<State>

Niedersachsen

</State>

</City>

<City>

Storing XML documents inside

the database as values of type XML

Generating XML documents using SQL/XML functions

– Datatype XML with belonging functions – Mapping between

SQL and XML

– Embedding XQuery in SQL

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig 5 [Tür08]

SQL database XML datatype SQL XQuery

Mapping between SQl and XML

(6)

• Mapping SQL database to XML

– SQL charset to unicode (depends on implementation) – SQL identifiers to XML names

– SQL data types to XML schema data types

6.1 Introduction

SQL data types to XML schema data types

– SQL values to XML values

– SQL tables to XML and XML schema documents – SQL schemas to XML and XML schema documents – SQL catalogues to XML and XML schema documents

(7)

Name Balance

Joe 2000

Jim 3500

Mapping SQL tables <xsd:complexType name="ROW.ACCOUNT">

<xsd:sequence>

<xsd:element name="NAME"

type="CHAR_20"/>

<xsd:element name="BALANCE"

type="NUMERIC_12_2"/>

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="TABLE.ACCOUNT">

<xsd:annotation><xsd:appinfo>

<xqlxml:sqlname type="BASE TABLE"

CREATE TABLE Account (

Name CHAR(20),

Balance NUMERIC(12,2), );

Mapping SQL table columns to XML elements

Mapping table rows to XML

<row>

elements

Jim 3500

<ACCOUNT>

<row>

<NAME>Joe</NAME>

<BALANCE>2000</BALANCE>

</row>

<row>

<NAME>Jim</NAME>

<BALANCE>3500</BALANCE>

</row>

</ACCOUNT>

localName="ACCOUNT"/>

</xsd:appinfo></xsd:annotation>

<xsd:sequence>

<xsd:element name="row"

type="ROW.ACCOUNT"/>

</xsd:sequence>

</xsd:complexType>

<xsd:element name="ACCOUNT"

type="TABLE.ACCOUNT"/>

elements

(8)

• Relational table: Cities

6.1 Introduction

City Zip State

Braunschweig 38100 Niedersachsen

Braunschweig 38106 Niedersachsen

Hannover 30159 Niedersachsen

• Many possible XML documents

...

<City>

<Name>Braunschweig</Name>

<Zip>38100</Zip>

<Zip>38106</Zip>

<State>Niedersachsen</State>

</City>

...

<State name="Niedersachsen">

<City name="Braunschweig">

<Zip>38100</Zip>

<Zip>38106</Zip>

</City>

</State>

...

(9)

6.1Introduction

6.4 Queries

6. SQL/XML

6.8 References

(10)

• XMLELEMENT creates an XML element

– Example: creating name and content

6.2 Publishing relational data

XMLELEMENT( NAME "City", 'Bad Oeynhausen' ) Creates

<City>Bad Oeynhausen</City>

– Can contain attributes, comments and other elements and options

<City>Bad Oeynhausen</City>

XMLELEMENT( NAME "City", XMLCOMMENT ( "Example 2" ), XMLATTRIBUTES('Bayern' AS "State",

'80469' AS "Zip" ),'München' ) Creates

<City State="Bayern" Zip="80469"><!– Example 2 -->

München</City>

(11)

• XMLELEMENT referencing the database

– Can be used directly from an SQL statement

6.2 Publishing relational data

SELECT XMLELEMENT( NAME "City",

XMLCOMMENT ( "Example 3" ),

XMLATTRIBUTES( "State", "Zip" AS "PLZ" ),

"City" )

• Creates

"City" ) FROM Cities WHERE …;

<City STATE="Niedersachsen" PLZ="38100">

<!– Example 3 -->

Braunschweig

</City>

(12)

• XMLELEMENT nesting

– Example

6.2 Publishing relational data

SELECT XMLELEMENT( NAME "City",

XMLELEMENT( NAME "Name", "City" ), XMLELEMENT( NAME "State", "State" ), XMLELEMENT( NAME "Zip", "Zip" ) )

• Creates

XMLELEMENT( NAME "Zip", "Zip" ) ) FROM Cities WHERE …;

<City>

<Name>Braunschweig</Name>

<State>Niedersachsen</State>

<Zip>38100</Zip>

</City>

(13)

• XMLELEMENT syntax diagram

6.2 Publishing relational data

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig 13 [IBM]

(14)

• XMLFOREST

– Constructs a forest of elements without attributes

• Creates

6.2 Publishing relational data

SELECT XMLFOREST ( "City", "State" ) FROM Cities;

• Creates

<City>Braunschweig</City><State>Niedersachsen</State>

<City>Hannover</City><State>Niedersachsen</State>

(15)

• XMLFOREST syntax diagram

6.2 Publishing relational data

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig 15 [IBM]

(16)

• XMLCONCAT

– Concatenates multiple XML fragments into a single XML pattern

– Compare outputs

6.2 Publishing relational data

SELECT XMLELEMENT("city", City) AS "CITY",

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig 16 [Pow07]

SELECT XMLELEMENT("city", City) AS "CITY", XMLELEMENT("zip", Zip) AS "ZIP",

XMLELEMENT("state", State) AS "STATE"

FROM Cities;

SELECT XMLCONCAT(

XMLELEMENT("city", CITY), XMLELEMENT("zip", ZIP),

XMLELEMENT("state", STATE) ) FROM Cities;

(17)

• XMLAGG

– Aggregates seperate lines of output into a single string

6.2 Publishing relational data

SELECT CITY, XMLAGG(

XMLELEMENT(NAME "Zip", Zip)) AS

"Zipcodes"

• Creates

City Zipcodes

Braunschweig <Zip>38100</Zip>

<Zip>38106</Zip>

Hannover <Zip>30159</Zip>

FROM Cities

GROUP BY City;

(18)

• XMLAGG

– Allows sorting

6.2 Publishing relational data

SELECT XMLAGG(

XMLELEMENT("address", Zip||' '||City) ORDER BY Zip DESC)

FROM Cities;

• Creates

– Disadvantage: Can only aggregate a single element, and thus fields are concatenated

FROM Cities;

<address>38106 Braunschweig</address>

<address>38100 Braunschweig</address>

<address>30159 Hannover</address>

(19)

6.1Introduction

6.4 Queries

6. SQL/XML

6.8 References

(20)

• Storing XML in relational databases is possible as

– Character data (VARCHAR, Character Large OBject) – New data type XML

• A value of the data type XML can contain

– whole XML document

6.3 XML data type

– whole XML document – XML element

– a set of XML elements

• All XML publishing operators from chapter 6.2 create values of the data type XML, not a string

(21)

6.3 XML data type

XML(SEQUENCE)

XML(CONTENT(ANY))

NULL or document node Untyped elements &

attributes,

elements not NULL 1 element child

Validated against schema

XML(CONTENT(XMLSCHEMA)) XML(CONTENT(UNTYPED))

XML(DOCUMENT(ANY))

XML(DOCUMENT(UNTYPED)) XML(DOCUMENT(XMLSCHEMA))

1 element child Validated against schema

1 element child

(22)

• Specification of XML type

• Modifiers are optional

• Primary type modifier

6.3 XML data type

XML [({DOCUMENT|CONTENT|SEQUENCE}

[({ANY|UNTYPED|XMLSCHEMA schema name})])]

• Primary type modifier

– DOCUMENT (XML document) – CONTENT (XML element)

– SEQUENCE (sequence of XML elements)

• Secondary type modifier

– UNTYPED

– XMLSCHEMA (typed) – ANY (may be typed)

(23)

• Create a table that is an XML data type in itself

– CREATE TABLE XMLDOCUMENT OF XMLTYPE;

• Create a table containing an XMLType data type column

6.3 XML data type

column

CREATE TABLE XML (

ID NUMBER NOT NULL,

XML XMLTYPE,

CONSTRAINT XPK PRIMARY KEY (ID) );

(24)

• Example: Definition of an XML type column

6.3 XML data type

ID Name

123 <Groups>Annabelle</Groups>

CREATE TABLE Groups (

ID INTEGER,

Name XML );

123 <Groups>Annabelle</Groups>

234 <Groups>Magdalena, Marius</Groups>

345 <?xml version 1.0?>

<Person>Patrick</Person>

<Person>Robert</Person>

</Groups>

654 <Groups>Rebecca</Groups>

<Groups>Torben</Groups>

(25)

• Characteristics

– Allowed values:

• XML documents (including prolog)

• XML content according to XML 1.0 (includes pure text comments, PI?)

• NULL

6.3 XML data type

• NULL

– No comparison possible (compare CLOB in SQL)

• User can define an order, if comparison is necessary

– No corresponding type in programming languages for embedding in SQL available

– Standard defines operators to convert to other SQL data types

(26)

• Parsing & Serialization

– XMLParse:

• Parses a string value using an XML parser

• Produces value whose specific type is

6.3 XML data type

<City>

<Name>

Braunschweig

</Name>

<Zip>38100</Zip>

<Zip>38106</Zip>

<State>

Niedersachsen

</State>

</City>

• Produces value whose specific type is

XML(DOCUMENT(ANY)), or …CONTENT…, or

– XMLSerialize

• Transforms an XML value into a string value (CHAR, VARCHAR, CLOB, or BLOB)

XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig 26 [Mel05]

<City>

<Name>

Braunschweig

</Name>

<Zip>38100</Zip>

<Zip>38106</Zip>

<State>

Niedersachsen

</State>

</City>

(27)

6.1Introduction

6.4 Queries

6. SQL/XML

6.8 References

(28)

• Motivation

– How can SQL applications

locate and retrieve information

in XML documents stored in an SQL database cell?

– Invoking XML query language within SQL statements

6.4 Queries

– Invoking XML query language within SQL statements

• Retrieve information — in SELECT list

• Locate information — in WHERE clause

– Details on XML query language XQuery later

(29)

• XMLQuery

– A new SQL expression, invoked as a pseudo- function, whose data type can be an XML type

—such as XML(CONTENT(ANY))—or an ordinary SQL type

6.4 Queries

ordinary SQL type

• XMLExists

– A new SQL predicate, invoked as a pseudo-function, returning true when the contained XQuery

expression returns anything other than the empty sequence (false) or SQL null value (unknown)

(30)

• XMLQuery syntax

•

6.4 Queries

XMLQUERY(<XQuery expression>

[PASSING <argument list>]

{NULL | EMPTY} ON EMPTY)

argument list := <SQL value> AS <XQuery variable>

– Example

•

CityList

<State name="Niedersachsen"><City>Braunschweig</City></State>

<State name="Niedersachsen"><City>Hannover</City></State>

SELECT XMLQUERY(

'<State name="{$Name}"><City>{$City}</City></State>' PASSING State as $Name, City AS $City NULL ON EMPTY) AS CityList FROM Cities;

(31)

6.4 Queries

ID ^Paper

123 <Paper>…<author>Alice</author><title>Perpetual Motion</title><year>1999</year></Paper>

345 <Paper><year>2005</year><author>Bob</author><author>Charlie

</author><title>Beer</title>…</Paper>

CREATE TABLE Papers (ID INTEGER, Paper XML);

</author><title>Beer</title>…</Paper>

ID AuthorNames

123 <Authors>Alice</Authors>

345 <Authors>Bob</Authors>

<Authors>Charlie</Authors>

SELECT ID, XMLQUERY(

'FOR $a IN $p//author RETURN

<Authors>{$a/text()}</Authors>' PASSING Paper AS "p") AS AuthorNames FROM Papers;

(32)

• XMLTABLE

– Provides an SQL view of XML data

• Output is not of the XML type

– Evaluates an XQuery “row pattern” with optional arguments (as with XMLQuery)

– Element/attribute values mapped to columns using XQuery

“column patterns”

6.4 Queries

Element/attribute values mapped to columns using XQuery

“column patterns”

– Names & types of columns required; default values optional – Syntax:

XMLTABLE (<XQuery expression>

PASSING <argument list>

COLUMNS <column list>)

column := <name> <type> PATH <path expression>

(33)

• XMLTable: Example

6.4 Queries

SELECT ID, t.*

FROM Papers p, XMLTABLE(

'for $root in $papers

where $root//author/text() = "Bob"

return $root/Paper'

PASSING p.Paper as "papers"

ID About Created

345 Beer 2005

PASSING p.Paper as "papers"

COLUMNS

About VARCHAR(30) PATH '/Paper/title', Created INTEGER PATH '/Paper/year'

) AS t;

(34)

6.1Introduction

6.4 Queries

6. SQL/XML

6.8 References

(35)

• Validation of XML

– Is like integrity constraints in DBs – Requires an XML Schema

– XML Schemas may be registered with the SQL-server

6.5 Validation

XML Schemas may be registered with the SQL-server

• Implementation-defined mechanism

• Known by SQL name & by target namespace URI

– Schema does need a unique name

• Used by XMLValidate(), IS VALID, and to restrict values of

XML(DOCUMENT-or-CONTENT(XMLSCHEMA))

(36)

• Schema registration

6.5 Validation

Register XMLSCHEMA

'http://www.Alfred-Moos.de/GrussSchema.xsd' FROM 'file://c:/XML_Schemata/GrussSchema.xsd' AS GrussSchema

COMPLETE

;

CREATE TABLE Dokument_XML

(Dokument_XML_Nr CHAR (4) NOT NULL

PRIMARY KEY, Dokument XML,

CONSTRAINT validieren

CHECK (Dokument IS VALIDATED ACCORDING TO XMLSCHEMA ID GrussSchema

) )

;

(37)

• Schema definition

– Syntax

6.5 Validation

XML(CONTENT(XMLSCHEMA) <schema> [<elements>]))

<schema> := URI <namespace> [LOCATION <loc>]

| NO NAMESPACE [LOCATION <loc>]

–

| NO NAMESPACE [LOCATION <loc>]

| ID <registered schema name>

<element> := [NAMESPACE <namespace>]

ELEMENT <element name>

(38)

• New functions and predicates:

– XMLValidate

• Validates an XML value against an XML Schema (or target namespace), returning new XML value with type annotations

– IS VALID

• Tests an XML value to determine whether or not it is valid

6.5 Validation

• Tests an XML value to determine whether or not it is valid according to an XML Schema (or target namespace); return true/false without altering the XML value itself

– IS DOCUMENT

• determines whether an XML value satisfies the (SQL/XML) criteria for an XML document

– IS CONTENT

• determines whether an XML value satisfies the (SQL/XML) criteria for XML content

(39)

• Benefits of schema registration

– Security issues

• Schemas cannot “disappear” without SQL- server knowing about it

• Schemas cannot be “hijacked” (altered in

6.5 Validation

• Schemas cannot be “hijacked” (altered in inappropriate ways) without SQL-server knowing about it

• Documents cannot be marked “valid”

against schemas unless SQL-server knows about them

(40)

• Predefined schemas (build-in namespaces)

– xs:http://www.w3.org/2001/XMLSchema

– xsi:http://www.w3.org/2001/XMLSchema-instance

– sqlxml:http://standards.iso.org/iso/9075/2003/sqlxml

– More depending on the DB implementation

6.5 Validation

More depending on the DB implementation

• Completely supported per XML+Namespaces:

– XMLElement, XMLForest, XMLTable

– Default namespace, explicit namespace (prefix)

– Declare namespace within scopes of WITH clause, column definitions, constraint definitions, insert/delete/update

statements, compound statements

(41)

6.1Introduction

6.4 Queries

6. SQL/XML

6.8 References

(42)

• SQL/XML standard published as

– ISO/IEC 9074-14:2003

• Mappings and Publishing Functions

– ISO/IEC 9075-14:2006

• Adds XQuery, including Data Model, Validation

6.6 SQL/XML standard

• Adds XQuery, including Data Model, Validation

– ISO/IEC 9075-14:2008

• Updates

• Something else?

(43)

• SQL/XML:2003 plus

– Additional publishing functions – XQuery data model

– More precise XML type (modifiers) – XMLQuery, XMLTable

6.6 SQL/XML standard

– XMLQuery, XMLTable – XMLValidate, IS VALID

– XMLExists, IS DOCUMENT, IS CONTENT – Casting between XML type and SQL types

(44)

• Overview of some operators for the XML type

– XMLELEMENT – creates an XML element node

– XMLFOREST – creates a sequence of XML element nodes from a table

6.6 SQL/XML standard

from a table

– XMLCOMMENT – creates an XML comment node – XMLTEXT – creates a text node

– XMLPI – creates a processing instruction

– XMLAGG – aggregates XML values of a group – XMLCONCAT – concatenates XML type values – XMLTRANSFORM – applies an XSL to a document

(45)

• ... Overview of some operators for the XML type

– XMLPARSE – a well-formed SQL text to XML value

– XMLSERIALIZE – converts an XML value to a SQL text – XMLDOCUMENT – creates an XML document node

6.6 SQL/XML standard

– XMLDOCUMENT – creates an XML document node from an XML value

– XMLVALIDATE – validates an XML value with a schema – XMLQUERY – evaluates an XQuery expression

– XMLTABLE – transforms an XQuery result to a SQL table – XMLITERATE – transforms an XQery sequence to a SQL

table

(46)

• Review of SQL/XML

– Two components

• A data type XML to store XML data

• Functions to map relational structures to XML

– Only construction operators

• No extraction of values or search

6.6 SQL/XML standard

• No extraction of values or search

• But construction operators are based on XQuery

– Mapping of tables, schemas, catalogues ignores some information from the relational schema

• UNIQUE

• REFERENCES

• CHECK

– Further extensions are expected

(47)

1. Introduction 2. XML Basics

3. Schema definition

4. XML query languages I 5. Mapping relational data

8. XML query languages II 9. XML storage I

10. XML storage - index 11. XML storage - native

6.7 Overview

5. Mapping relational data to XML

6. SQL/XML

7. XML processing

11. XML storage - native 12. Updates / Transactions 13. Systems

14. XML Benchmarks

47 XML Databases – Silke Eckstein – Institut für Informationssysteme – TU Braunschweig

(48)

• "XML und Datenbanken" [Tür08]

– Can Türker

– Lecture, University of Zurich, 2008

• Beginning XML Databases. [Pow07]

– Gavin Powell

Wiley & Sons, 2007, ISBN 0471791202

6.8 References

– Wiley & Sons, 2007, ISBN 0471791202

• "XML-Datenbanken", [Kud07]

– Thomas Kudraß

– Lecture, HTWK Leipzig, WS2007/2008

• "SQL/XML", [Mel05]

– Jim Melton,

– Oracle Corp. 2005

(49)

• XQuery und SQL/XML in DB2-Datenbanken:

Verwaltung und Erzeugung von XML- Dokumenten in DB2 [Moo08]

– Alfred Moos

6.8 References

– Vieweg+Teubner, 2008

• ISO/IEC 9075-14:2003 Information Technology -

Database Languages - SQL - Part 14: XML-Related Specifications (SQL/XML)

• DB2 SQL-Reference, IBM, March 2008 [IBM]

(50)

• Now, or ...

• Room: IZ 232

• Office our: Tuesday, 12:30 – 13:30 Uhr

Questions, Ideas, Comments

• Office our: Tuesday, 12:30 – 13:30 Uhr or on appointment

• Email: eckstein@ifis.cs.tu-bs.de